Example: JSON_SHRED_BATCH Using JSONID and ROWINDEX Keywords - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
The example shows the use of the JSONID and ROWINDEX keywords.
Before running this example, make sure you have SELECT privilege on the source table (jsonshred.JSONDocs) and INSERT privilege on the target table (jsonshred.Teradata_Employees).
CALL SYSLIB.JSON_SHRED_BATCH(
   'SELECT * FROM jsonshred.JSONDocs',
   NEW JSON('[
   {
   "rowexpr" : "$.employees.info[*]",
   "colexpr" : [{"col1" : "$..id", "type" : "INTEGER"},
                {"col2" : "$.employees.company", "type" : "VARCHAR(15)","fromRoot":true},
                {"col3" : "$..name", "type" : "VARCHAR(20)"},
                {"col4" : "$..dept", "type" : "VARCHAR(20)"}],
   "queryexpr" : [{"qrycol1" : "varchar(20)"},{"qrycol2" : "varchar(20)"}],
   "tables" : [
                {"jsonshred.Teradata_Employees" : { 
                "metadata" : { "operation" : "insert","keys" : ["empId"] },
                "columns" :  {"rowIndex" : "ROWINDEX",
                              "empId" : "col1+1000",
                              "empName" : "col3",
                              "jsonDocId" : "JSONID",
                              "company" : "col2",
                              "dept" : "col4",
                              "site" : "qrycol1",
                              "country" : "qrycol2" }
                             }
                }
              ]
   }]', LATIN), :res);
To see the result, run: SELECT * from jsonshred.Teradata_Employees order by rowindex;
rowIndex   empId  empName      company      dept         jsonDocId  site    country
--------  ------  -----------  -----------  -----------  ---------  ------  -------
       0    1001  Cameron      Teradata     engineering          1  HYD     USA
       1    1002  Justin       Teradata     engineering          1  HYD     USA
       2    1003  Madhu        Teradata     engineering          2  HYD     USA
       3    1004  Srini        Teradata     engineering          2  HYD     USA