Modifying JSON Columns

Teradata Vantageā„¢ JSON Data Type

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

You can use the following statements to modify a JSON column:

  • UPDATE. You can use the UPDATE statement to modify values of a JSON column in the SET clause.
    You can only update the JSON column, not individual portions of the JSON instance. Also, when referencing a JSON column in its entirety, the input format is the same as the INSERT statement.
  • MERGE. You can use the MERGE statement to:
    • Modify a JSON column based on the WHEN MATCHED THEN UPDATE clause.
    • Insert values into a JSON column based on the WHEN NOT MATCHED THEN INSERT clause.
      The following MERGE statement clauses are supported on JSON functions and methods:
      • The ON clause can be used to condition on a JSON instance.
      • The USING clause can be used to select the JSON column value.

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" : 8584852611}');
INSERT INTO my_table(2,'{"name" : "Cameron", "phoneNumber" : 8584852612}');

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" : 8584852613}';
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
---------
1   {"name" : "Kevin", "phoneNumber" : 8584852613}
2   {"name" : "Cameron", "phoneNumber" : 8584852612}

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" : 8584852614}'));
To see the result of the query, run: SELECT * FROM my_table ORDER BY eno;
eno edata
-----------
1   {"name" : "Kevin", "phoneNumber" : 8584852613}
2   {"name" : "Cameron", "phoneNumber" : 8584852612}
2   {"name" : "Mike", "phoneNumber" : 8584852614}