1.1 - 8.10 - JSONParser Example: Default Values - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
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 ;

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.