Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
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; |
Attachments
Issue Links
- relates to
-
-
- Closed
-