Example: Populate Multiple Tables from a JSON Object Using JSON_SHRED_BATCH

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
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