JSON in the Oracle/ Validate JSON in Oracle column

CREATE TABLE colorTab (
id NUMBER,
color VARCHAR2(32767) or CLOB);

INSERT INTO colorTab VALUES(1, ‘
{
“color”: “black”,
“rgb”: [0,0,0],
“hex”: “#000000”
}’);

INSERT INTO colorTab VALUES(2, ‘
{
“color”: “orange red”,
“rgb”: [255,69,0],
“hex”: “#FF4500”
}’);

JSON operator: ‘IS JSON’. It allows us to filter column values

SELECT id, color
FROM colorTab
WHERE color IS JSON;

Running IS JSON in a strict mode

SELECT id, color
FROM colorTab
WHERE color IS JSON STRICT;

The clause ‘WITH UNIQUE KEYS’ checks that keys

SELECT id, color
FROM colorTab
WHERE color IS JSON STRICT WITH UNIQUE KEYS;

Lets add a constraint to our table:

TRUNCATE TABLE colorTab;

ALTER TABLE colorTab
ADD CONSTRAINT ensure_json CHECK (color IS JSON STRICT);

Now, when inserting any non-JSON row will be rejected with
ORA-02290: check constraint (SYS.ENSURE_JSON) violated

Advertisements