Month: June 2025

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.

Oracle has had DEFAULT column values for a long time. But there was an annoying gap: if you explicitly INSERT NULL into a column that has a default, Oracle uses NULL — not the default. The default only applies when the column is omitted from the INSERT statement entirely.

Oracle 23ai closes this gap with DEFAULT ON NULL.

Classic DEFAULT behavior (still applies):

CREATE TABLE requests (
    id       NUMBER,
    priority VARCHAR2(10) DEFAULT 'NORMAL'
);

-- Omitting priority: uses default → 'NORMAL'
INSERT INTO requests (id) VALUES (1);

-- Explicitly inserting NULL: stores NULL (default ignored)
INSERT INTO requests (id, priority) VALUES (2, NULL);

With DEFAULT ON NULL:

CREATE TABLE requests (
    id       NUMBER,
    priority VARCHAR2(10) DEFAULT ON NULL 'NORMAL'
);

-- Explicitly inserting NULL: uses default → 'NORMAL'
INSERT INTO requests (id, priority) VALUES (2, NULL);

-- NULL can no longer be stored in this column via INSERT or UPDATE

Adding to an existing column:

ALTER TABLE requests MODIFY priority DEFAULT ON NULL 'NORMAL';

Real-world uses:

• Audit columns like created_by that should always have a value
• Status columns where NULL is never a valid state
• Configuration values with meaningful defaults that should survive explicit NULL inserts from ORMs or API frameworks that always pass all columns

This bridges the semantic gap between application-layer “I didn’t provide a value” and “I provided NULL,” which are often treated the same way by ORMs, REST frameworks, and bulk insert tools.

Oracle 23ai adds four new statistical aggregate functions that give SQL developers direct access to distribution shape analysis without leaving the database.

SKEWNESS_POP and SKEWNESS_SAMP

Skewness measures the asymmetry of a distribution around its mean.

• Positive skew: long tail on the right (e.g., most employees earn average salaries, but a few earn very high)
• Negative skew: long tail on the left
• Zero: symmetric distribution

SELECT
    department_id,
    ROUND(SKEWNESS_POP(salary), 4)  AS pop_skewness,
    ROUND(SKEWNESS_SAMP(salary), 4) AS sample_skewness,
    COUNT(*) AS headcount
FROM employees
GROUP BY department_id
ORDER BY ABS(SKEWNESS_SAMP(salary)) DESC;

Use SKEWNESS_POP when you have the full population. Use SKEWNESS_SAMP when working with a sample (applies Bessel’s correction).

KURTOSIS_POP and KURTOSIS_SAMP

Kurtosis measures the “tailedness” of a distribution — whether extreme values are frequent or rare compared to a normal distribution.

SELECT
    product_category,
    ROUND(KURTOSIS_SAMP(order_value), 4) AS kurtosis,
    STDDEV(order_value)                  AS std_deviation,
    AVG(order_value)                     AS mean_value
FROM orders
GROUP BY product_category
HAVING COUNT(*) > 100;  -- meaningful only with sufficient data

High kurtosis means more extreme outliers than expected. This is important for financial risk modeling, anomaly detection, and capacity planning.

Before 23ai: You had to calculate skewness and kurtosis manually using complex expressions involving SUM(POWER(x - mean, 3)) and normalization factors. Or export data to Python/R. Now it’s a single function call in SQL, composable with GROUP BY and window functions.