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 preceding shred populates the emp_table and dept_table tables with three rows, corresponding to the three items in the JSON object used as source data.
To view the data inserted into the employee table:
SELECT * FROM emp_table ORDER BY empID;
Result:
empID company empName empAge dept startDate site ----- ------------ --------- ------ ------------ ---------- ----- 100 company1 Cameron 24 engineering 15/02/10 RB 200 company1 Justin 30 engineering 15/02/07 RB 300 company1 Melissa 24 marketing ? RB
To view the data inserted into the department table:
SELECT * FROM dept_table ORDER BY dept, empID;
Result:
dept description empID -------------------------------------- engineering CONSTANT DESCRIPTION 1 engineering CONSTANT DESCRIPTION 2 marketing CONSTANT DESCRIPTION 3