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 ORDER BY 1, 2;
Output
| id | out_nodeid | out_parent_node | glossary:title | GlossDiv:title | GlossEntry:Abbrev | GlossSeeAlso:GlossSeeAlso | |
|---|---|---|---|---|---|---|---|
| 1 | 1 | glossary | example glossary | ISO 8879:1986 | |||
| 1 | 2 | GlossDiv | S | GML, XML | |||
| 1 | 3 | GlossEntry | fred@glossary.com | ||||
| 1 | 4 | GlossSeeAlso | |||||
| 1 | 5 |