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