Modifying JSON Columns Examples | Teradata Vantage - Modifying JSON Columns 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ā„¢

Setting Up the Update Examples

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

CREATE TABLE my_table (eno INTEGER, edata JSON(100));
INSERT INTO my_table (1, NEW JSON('{"name" : "Cameron", "age" : 24}'));

Example: UPDATE and SET

The following statement sets the edata column value and updates the table.
UPDATE my_table
SET edata = NEW JSON('{"name" : "Justin"}')
WHERE edata.JSONExtractValue('$.name') = 'Cameron';
Result: To see the result, run: SELECT edata FROM my_table;
edata
-----
{"name" : "Justin"}

Example: UPDATE Column Value

The following statement sets the edata column value and updates the table.
UPDATE my_table
SET edata = '{"name" : "George"}';
Result: To see the name has been updated, run: SELECT edata FROM my_table;
edata
-----
{"name" : "George"}

Setting Up the Merge Examples

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

CREATE TABLE my_table (eno INTEGER, edata JSON(100)); 
INSERT INTO my_table(1,'{"name" : "Justin", "phoneNumber" : 9595552611}');
INSERT INTO my_table(2,'{"name" : "Cameron", "phoneNumber" : 9595552612}');

CREATE TABLE my_table2 (eno INTEGER, edata JSON(100)); 
INSERT INTO my_table2(1,'{"age":24}');
INSERT INTO my_table2(3,'{"age":34}');

Example: MERGE When Matched Then Update

The example shows how to use the MERGE WHEN MATCHED THEN UPDATE clause.
MERGE INTO my_table
USING (SELECT eno, edata FROM my_table2) AS S(a,b)
ON eno = S.a AND 
 CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Justin'
WHEN MATCHED THEN UPDATE
 SET edata = '{"name" : "Kevin", "phoneNumber" : 9595552613}';
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
---------
1   {"name" : "Kevin", "phoneNumber" : 9595552613}
2   {"name" : "Cameron", "phoneNumber" : 9595552612}

Example: MERGE When Not Matched Then Insert

The example shows how to use the MERGE WHEN NOT MATCHED THEN INSERT clause.
MERGE INTO my_table
USING (SELECT eno, edata FROM my_table2) AS S(a,b)
ON eno = S.a AND
CAST (edata.JSONExtractValue('$.name') AS VARCHAR(20)) = 'Mike'
WHEN NOT MATCHED THEN INSERT (eno, edata)
VALUES (S.a, NEW JSON('{"name" : "Mike", "phoneNumber" : 9595552614}'));
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
-----------
1   {"name" : "Kevin", "phoneNumber" : 9595552613}
2   {"name" : "Cameron", "phoneNumber" : 9595552612}
2   {"name" : "Mike", "phoneNumber" : 9595552614}