Input
The input table, json_parser_data, is a single JSON record with multiple fields.
id | data |
---|---|
1 |
{"menu": { "id": "1", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } }} |
SQL Call
NodeIDOutputColumnName and ParentNodeOutputColumnName have nondefault values.
SELECT * FROM JSONParser ( ON json_parser_data USING TextColumn ('data1') Nodes ('menu/{id,value}', 'menuitem/value') Delimiter ('|') NodeIDOutputColumn ('Fieldnumber') ParentNodeOutputColumn ('ParentName') Accumulate ('id') ) AS dt ORDER BY 1, 2;
Output
id | fieldnumber | parentname | menu:id | menu:value | menuitem:value |
---|---|---|---|---|---|
1 | 1 | menu | 1 | File | |
1 | 2 | menuitem | New|Open|Close |