1.0 - 8.00 - JSONParser Example 2: Default Values - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

Input

The input table, json_parser_data_2, is a single JSON record with multiple fields.

json_parser_data_2
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 :email
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