Example: Use JSON_SHRED_BATCH to Update a Table from a JSON Object

Teradata Vantage™ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

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