: [32, 34, 36]},
 {"name":"T-Shirt", "sizes":["Medium", "Large"]},
 {"name":"Cellphone"}
]';

NESTED PATH allows one to produce a separate row for each size each item has:

select * from json_table(@json, '$[*]' 
 columns(
  name  varchar(10) path '$.name',
  nested path '$.sizes[*]' columns (
   size varchar(32) path '$'
  )
 )
) as jt;
+-----------+--------+
| name      | size   |
+-----------+--------+
| Jeans     | 32     |
| Jeans     | 34     |
| Jeans     | 36     |
| T-Shirt   | Medium |
| T-Shirt   | Large  |
| Cellphone | NULL   |
+-----------+--------+

NESTED PATH clauses can be nested within one another. They can also be located
next to each other. In that case, the nested path clauses will produce records
one at a time. The ones that are not producing records will have all columns
set to NULL.

Example:

set @json='
[
 {"name":"Jeans",  "sizes": [32, 34, 36], "colors":["black", "blue"]}
]';

select * from json_table(@json, '$[*]' 
 columns(
  name  varchar(10) path '$.name',
  nested path '$.sizes[*]' columns (
   size varchar(32) path '$'
  ),
  nested path '$.colors[*]' columns (
   color varchar(32) path '$'
  )
 )
) as jt;

+-------+------+-------+
| name  | size | color |
+-------+------+-------+
| Jeans | 32   | NULL  |
| Jeans | 34   | NULL  |
| Jeans | 36   | NULL  |
| Jeans | NULL | black |
| Jeans | NULL | blue  |
+-------+------+-------+

ON EMPTY and ON ERROR Clauses
-----------------------------

The ON EMPTY clause specifies what will be done when the element specified by
the search path is missing in the JSON document.

on_empty:
  {NULL | DEFAULT string | ERROR} ON EMPTY

When ON EMPTY clause is not present, NULL ON EMPTY is implied.

on_error:
  {NULL | DEFAULT string | ERROR} ON ERROR

The ON ERROR clause specifies what should be done if a JSON structure error
occurs when trying to extract the value pointed to by the path expression. A
JSON structure error here occurs only when one attempts to convert a JSON
non-scalar (array or object) into a scalar value. When the ON ERROR clause is
not present, NULL ON ERROR is implied.

Note: A datatype conversion error (e.g. attempt to store a non-integer value
into an integer field, or a varchar column being truncated) is not considered
a JSON error and so will not trigger the ON ERROR behavior. It will produce
warnings, in the same way as CAST(value AS datatype) would.

Replication
-----------

In the current code, evaluation of JSON_TABLE is deterministic, that is, for a
given input string JSON_TABLE will always produce the same set of rows in the
same order. However, one can think of JSON documents that one can consider
identical which will produce different output. In order to be future-proof and
withstand changes like:

* sorting JSON object members by name (like MySQL does)
* changing the way duplicate object members are handled
the function is marked as unsafe for statement-based replication.

Extracting a Subdocument into a Column
--------------------------------------

MariaDB starting with 10.6.9
----------------------------
Prior to MariaDB 10.6.9, JSON_TABLE did not allow one to extract a JSON
"subdocument" into a JSON column.

SELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path
'$.foo') ) AS T;
+-------+
| jscol |
+-------+
| NULL  |
+-------+

This is supported from MariaDB 10.6.9:

SELECT * FROM JSON_TABLE('{"foo": [1,2,3,4]}','$' columns( jscol json path
'$.foo') ) AS T;
+-----------+
| jscol     |
+-----------+
| [1,2,3,4] |
+-----------+

URL: https://mariadb.com/kb/en/json_table/https://mariadb.com/kb/en/json_table/