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.