Oracle has had JSON support since 12c, but 23ai significantly extends the JSON function library and makes JSON more deeply integrated with SQL. Here’s a tour of what’s new.
JSON_MERGEPATCH — patching JSON documents:
UPDATE preferences
SET settings = JSON_MERGEPATCH(
settings,
'{"theme": "dark", "notifications": {"email": false}}'
)
WHERE user_id = 42;JSON_MERGEPATCH follows RFC 7396 — it merges a patch document into an existing JSON value, replacing changed keys and removing keys set to null.
JSON_TRANSFORM — targeted updates:
UPDATE documents
SET data = JSON_TRANSFORM(
data,
SET '$.status' = 'archived',
SET '$.archived_at' = SYSTIMESTAMP,
REMOVE '$.draft_notes'
)
WHERE data.status = 'published';JSON_TRANSFORM lets you SET, REMOVE, APPEND, and REPLACE specific paths in a JSON document without rewriting the entire value.
IS JSON CHECK constraint with schema:
CREATE TABLE events (
event_id NUMBER,
payload CLOB
CONSTRAINT payload_is_json CHECK (payload IS JSON)
CONSTRAINT payload_schema CHECK (
payload IS JSON VALIDATE '{
"type": "object",
"required": ["event_type", "timestamp"],
"properties": {
"event_type": {"type": "string"},
"timestamp": {"type": "string", "format": "date-time"}
}
}'
)
);JSON_SCHEMA_VALIDATE: Validate a JSON value against a JSON Schema (draft-07 compatible) at query time.
SELECT JSON_SCHEMA_VALIDATE(payload, :my_schema) AS is_valid
FROM events;JSON capabilities in Oracle 23ai are now comparable to dedicated document databases, while maintaining full ACID compliance and relational query capabilities. A strong foundation for hybrid workloads.
