JSONParser Example: Parse with Ancestor | Teradata Vantage - JSONParser Example: Parse with Ancestor - Teradata Vantage

Input

The input table is json_parser_data_2, as in JSONParser Example: Default Values.

SQL Call

SELECT * FROM JSONParser(
  ON json_parser_data_2
  USING
  TextColumn ('data1')
  
  Nodes ('GlossEntry/ID','GlossEntry/SortAs','GlossEntry/GlossTerm',
        'GlossEntry/Acronym','/email')
  SearchPath('/glossary/GlossDiv/GlossList')

  Delimiter (' | ')
  Accumulate ('id')
  MaxItemNum (10)
) AS dt ;

Output

Because the email field is not included in the specified search path, the email column is empty.

 id out_nodeid out_parent_node glossentry_id glossentry_sortas glossentry_glossterm                 glossentry_acronym _email 
 -- ---------- --------------- ------------- ----------------- ------------------------------------ ------------------ ------ 
  1          1 GlossEntry      SGML          NULL              NULL                                 NULL                     
  1          2 GlossEntry      NULL          SGML              NULL                                 NULL                     
  1          3 GlossEntry      NULL          NULL              Standard Generalized Markup Language NULL                     
  1          4 GlossEntry      NULL          NULL              NULL                                 SGML

Download a zip file of all examples and a SQL script file that creates their input tables.