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 ;
Output
id fieldnumber parentname menu_id menu_value menuitem_value -- ----------- ---------- ------- ---------- -------------- 1 1 menu 1 File 1 2 menuitem New|Open|Close
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.