15.10 - Example: JSON_SHRED_BATCH Using JSONID and ROWINDEX Keywords - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty
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