Example: JSON_SHRED_BATCH Using JSONID and ROWINDEX Keywords

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
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