The example uses the JSON_SHRED_BATCH Update operation to update a table from a JSON instance.
Assume some new data comes in which provides the actual start date of the three employees (previously we loaded the table with a default value). We can update that specific value using JSON_SHRED_BATCH with the query below.
Before running this example, make sure you have SELECT privilege on the source table ( json_table) and UPDATE privilege on the target table (emp_table).
CALL SYSLIB.JSON_SHRED_BATCH(
'SELECT id, empCompanyInfo FROM test.json_table',
'[
{
"rowexpr" : "$.startDates.info[*]",
"colexpr" : [
{"col1" : "$.id",
"type" : "INTEGER"},
{"col2" : "$.startDates.company",
"type" : "VARCHAR(15)",
"fromRoot" : true},
{"col3" : "$.startDate",
"type" : "VARCHAR(20)"}
],
"tables" : [
{
"test.emp_table" : {
"metadata" : {
"operation" : "update",
"keys" : [ "empID", "company" ]
},
"columns" : { "empID" : "col1*100",
"company" : "col2",
"startDate" : "col3" }
}
}
]
}
]', :res );
Result: To view the updated data in the employee table, run: SELECT empID, company, empName, empAge, startDate, site FROM emp_table ORDER BY empID;
empID company empName empAge startDate site ---------------------------------------------- 100 Teradata Cameron 24 15/02/10 RB 200 Teradata Justin 34 15/02/07 RB 300 Teradata Melissa 24 15/02/07 RB