The example populates a table using a JSON instance as the source data. The example shreds the JSON document to extract values from it and inserts the data into the employee table (emp_table).
Before running this example, make sure you have SELECT privilege on the source table ( json_table) and INSERT privilege on the target table (emp_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(10)"}],
"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" }
}
}]
}]', :res );
The result of the shred populates the emp_table table with three rows, corresponding to the three items in the JSON object used as source data.
To see the result, run: SELECT empID, company, empName, empAge, startDate, site FROM emp_table ORDER BY empID;
empID company empName empAge startDate site ---------------------------------------------- 100 Teradata Cameron 24 13/09/19 RB 200 Teradata Justin 34 13/09/19 RB 300 Teradata Melissa 24 13/09/19 RB