Example: JSON_SHRED_BATCH Extracts from a JSON Object and Inserts into a Table

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 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