<JSON table column definition> ::=

<JSON table ordinality column definition>

| <JSON table regular column definition>

| <JSON table formatted column definition>

| <JSON table nested columns>

<JSON table formatted column definition> ::=

<column name> <data type>

FORMAT <JSON representation>

[ PATH <JSON table column path specification> ]

[ <JSON table formatted column wrapper behavior> WRAPPER ]

[ <JSON table formatted column quotes behavior> QUOTES

 [ ON SCALAR STRING ] ]

[ <JSON table formatted column empty behavior> ON EMPTY ]

[ <JSON table formatted column error behavior> ON ERROR ]

<JSON representation> ::=

JSON [ ENCODING { UTF8 | UTF16 | UTF32 } ]

| <implementation-defined JSON representation option>

The standardized option is FORMAT JSON; implementations may also

support syntax such as FORMAT AVRO or FORMAT BSON.

SET @json='

[

  {"name":"Jeans",  "sizes": [32, 34, 36]},

  {"name":"T-Shirt", "sizes":["Medium", "Large"]},

  {"name":"Cellphone"}

]';

SELECT * FROM JSON_TABLE(@json, '$[*]' COLUMNS(

  name VARCHAR(10) PATH '$.name',

  sizes VARCHAR(100) FORMAT JSON PATH '$.sizes'

)) AS jt;

+-----------+---------------------+

| name      | sizes               |

+-----------+---------------------+

| Jeans     | [32, 34, 36]        |

| T-Shirt   | ["Medium", "Large"] |

| Cellphone | NULL                |

+-----------+---------------------+

SELECT jt.name, JSON_QUERY(@json, CONCAT('$[', jt.id - 1,'].sizes')) AS sizes FROM JSON_TABLE(@json, "$[*]" COLUMNS(

  id FOR ORDINALITY,

  name JSON PATH "$.name"

)) AS jt;