15.10 - Using the JSON Type in a DELETE or ABORT Statement - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty

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.