Setting up the JSON_SHRED_BATCH Examples - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
uwa1591040057999.ditamap
dita:ditavalPath
uwa1591040057999.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢

Create and populate table(s) to use in subsequent example(s).

CREATE TABLE emp_table (
	empID INTEGER,
	company	VARCHAR(50), 
	empName VARCHAR(20),
	empAge	INTEGER,
	dept VARCHAR(20),
	startDate DATE FORMAT 'YY/MM/DD',
	site VARCHAR(20))
PRIMARY INDEX (company, empID);

CREATE MULTISET TABLE dept_table (
	dept          VARCHAR(20),
	description   VARCHAR(200),
	empID         INTEGER);

CREATE TABLE json_table (
	id  INTEGER,
	empPersonalInfo	JSON(1000),
	empCompanyInfo JSON(1000),
	site VARCHAR(20));
Insert sample data into the table.
INSERT INTO json_table (1,
  '{"employees" : {
    "company" : "Teradata",
    "info" : 
      [ 
        { "id" : 1, 
          "name" : "Cameron", 
          "age" : 24,
          "dept" : "engineering"},
        { "id" : 2, 
          "name" : "Justin", 
          "age" : 30,
          "dept" : "engineering"},
        { "id" : 3, 
          "name" : "Melissa", 
          "age" : 24, 
          "dept" : "marketing"}
     ]}}',
  '{"startDates" : {
    "company" : "Teradata",
    "info" : 
      [ 
        {"id" : 1, "startDate" : "2015/02/10"},
        {"id" : 2, "startDate" : "2015/02/07"},
        {"id" : 3, "startDate" : null}
      ]}}', 'RB'
);