[#MDEV-25727] Add formatted column support to JSON

The SQL standard defines the following types for the column definition of a JSON_TABLE clause:

<JSON table column definition> ::=

<JSON table ordinality column definition>

| <JSON table regular column definition>

| <JSON table formatted column definition>

| <JSON table nested columns>

All types except the formatted ones are already implemented in 10.6.0 and the only one left out is formatted 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.

Adding at least the minimal implementation of the FORMAT JSON clause would already help greatly when using JSON_TABLE with JSON objects with deeply nested JSON values.

If implemented, I would expect it to behave as follows:

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;

The expected result:

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

| name      | sizes               |

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

| Jeans     | [32, 34, 36]        |

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

| Cellphone | NULL                |

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

Currently this can be emulated using JSON_QUERY and an ordinality column but the syntax is quite uncomfortable to use:

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;

[MDEV-25727] Add formatted column support to JSON_TABLE Created: 2021-05-19 Updated: 2022-12-04
Status:	Open
Project:	MariaDB Server
Component/s:	JSON
Fix Version/s:	None

[MDEV-25727] Add formatted column support to JSON_TABLE Created: 2021-05-19 Updated: 2022-12-04