Input
The input table, json_parser_data_2, is a single JSON record with multiple fields.
id | data |
---|---|
1 |
{ "email":"fred@glossary.com", "glossary": { "title": "example glossary", "GlossDiv": { "title": "S", "GlossList": { "GlossEntry": { "ID": "SGML", "SortAs": "SGML", "GlossTerm": "Standard Generalized Markup Language", "Acronym": "SGML", "Abbrev": "ISO 8879:1986", "GlossDef": { "para": "A meta-markup language", "GlossSeeAlso": ["GML", "XML"] }, "GlossSee": "markup" } } } } } |
SQL Call
NodeIDOutputColumnName and ParentNodeOutputColumnName have their default values.
SELECT * FROM JSONParser ( ON json_parser_data_2 USING TextColumn ('data1') Nodes ('glossary/title','GlossDiv/title','GlossEntry/Abbrev', 'GlossSeeAlso/GlossSeeAlso','/email') Delimiter (' , ') Accumulate ('id') NodeIdOutputColumn ('out_nodeid') ParentNodeOutputColumn ('out_parent_node') ) AS dt ;
Output
id out_nodeid out_parent_node glossary_title glossdiv_title glossentry_abbrev glossseealso_glossseealso _email -- ---------- --------------- ---------------- -------------- ----------------- ------------------------- ----------------- 1 1 glossary example glossary 1 2 GlossDiv S 1 3 GlossEntry ISO 8879:1986 1 4 GlossSeeAlso GML , XML 1 5 fred@glossary.com
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.