Using the JSON Type in a DELETE or ABORT Statement

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

You can use the DELETE and ABORT statements with the JSON types in the WHERE clause. These types can be cast to a predefined type that can have relational comparisons performed on it, or these types can make use of the system defined functions or methods to isolate individual portions for comparison.

Setting Up the DELETE Example

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":"Cameron","age":24}'); 

Example: Using the DELETE Statement

Selectively delete data that meets the criteria.

DELETE my_table WHERE CAST (edata.JSONExtractValue('$.age') AS INTEGER) = 24; 
To see the result of the DELETE, run: SELECT * FROM my_table;
*** No rows found 

Setting Up the ABORT Example

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":"Cameron","age":24}'); 

Example: Using the ABORT Statement

Abort on a portion of the JSON instance.

ABORT 'JSON Abort' 
FROM my_table 
WHERE CAST (edata.JSONExtractValue('$.age') AS INTEGER) = 24; 
Result:
*** Failure 3513 JSON Abort.