The example uses a single JSON_SHRED_BATCH call to populate two tables with data.
Before running this example, make sure you have SELECT privilege on the source table ( json_table) and INSERT privilege on the target tables (emp_table, dept_table).
CALL SYSLIB.JSON_SHRED_BATCH( 'SELECT id, empPersonalInfo, site FROM test.json_table', '[ { "rowexpr" : "$.employees.info[*]", "colexpr" : [ {"col1" : "$.id", "type" : "INTEGER"}, {"col2" : "$.employees.company", "type" : "VARCHAR(15)", "fromRoot" : true}, {"col3" : "$.name", "type" : "VARCHAR(20)"}, {"col4" : "$.age", "type" : "INTEGER"}, {"col5" : "$.dept", "type" : "VARCHAR(20)"} ], "queryexpr" : [ {"site" : "VARCHAR(20)"} ], "tables" : [ { "test.emp_table" : { "metadata" : { "operation" : "insert" }, "columns" : { "empID" : "col1*100", "company" : "col2", "empName" : "col3", "empAge" : "col4", "dept" : "col5", "startDate" : "CURRENT_DATE", "site" : "site" } } }, { "test.dept_table" : { "metadata" : { "operation" : "insert" }, "columns" : { "dept" : "col5", "description" : ["CONSTANT DESCRIPTION"], "empID" : "col1" } } } ] } ]', :res );
The result of the above shred will populate the emp_table and dept_table tables with three rows, corresponding to the three items in the JSON object used as source data.
Result: To view the data inserted into the employee table, run: SELECT * FROM emp_table ORDER BY empID;
empID company empName empAge dept startDate site ----- ------------ --------- ------ ------------ ---------- ----- 100 Teradata Cameron 24 engineering 15/02/10 RB 200 Teradata Justin 30 engineering 15/02/07 RB 300 Teradata Melissa 24 marketing ? RB
Result: To view the data inserted into the department table, run: SELECT * FROM dept_table ORDER BY dept, empID;
dept description empID -------------------------------------- engineering CONSTANT DESCRIPTION 1 engineering CONSTANT DESCRIPTION 2 marketing CONSTANT DESCRIPTION 3