Chapter 28: Modern Extensions and SQL 2023 Features

SQL continues to evolve beyond its original relational roots. Modern standards extend it with richer data types, JSON support, table value constructors, and new syntactic features that make queries more expressive and concise. SQL:2023 builds on earlier versions by standardizing patterns already used across major engines and introducing new capabilities for analytics and JSON handling.

JSON data types and functions

SQL:2023 consolidates JSON handling that many engines already implemented independently. It introduces a formal JSON data type and standardized functions for creation, extraction, and validation. This allows relational and semi structured data to coexist efficiently within the same schema.

Storing JSON values

Many engines (PostgreSQL, MySQL, SQL Server, Oracle) already provide a JSON or JSONB type. SQL:2023 defines a general form:

CREATE TABLE events (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  payload   JSON NOT NULL,
  received  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO events (payload)
VALUES (JSON_OBJECT('type': 'click', 'user': 'r@example.com', 'x': 120, 'y': 480));

The JSON_OBJECT constructor creates JSON text directly from SQL expressions. The standard defines JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG, and more.

Querying JSON fields

Use JSON_VALUE, JSON_QUERY, and JSON_EXISTS to extract or filter by parts of a JSON document.

SELECT JSON_VALUE(payload, '$.user') AS username
FROM events
WHERE JSON_VALUE(payload, '$.type') = 'click';

JSON_VALUE extracts a scalar; JSON_QUERY extracts an object or array; JSON_EXISTS tests for path existence.

Validation and constraints

The standard adds IS JSON for structural validation and defines JSON_SCHEMA_VALIDATION for checking against schema definitions (implementation dependent).

SELECT id
FROM events
WHERE NOT (payload IS JSON VALUE);

Indexing JSON data

Some engines support functional or path based indexes for fast JSON lookups. For example, PostgreSQL can index computed expressions:

CREATE INDEX ix_events_user
  ON events ((payload->>'user'));

Other engines use virtual columns to materialize JSON keys and index them. Standard SQL now allows generated columns based on JSON_VALUE().

Table value constructors

Table value constructors (TVCs) let you create in-memory sets of rows directly within a query. They are part of the SQL standard but expanded in SQL:2023 for better portability and syntax clarity.

Basic inline table construction

SELECT *
FROM (VALUES
  (1, 'Alice', 'UK'),
  (2, 'Bob', 'US'),
  (3, 'Chen', 'CN')
) AS temp(id, name, country);

This creates a virtual table for quick testing or joins without a permanent table. It is especially useful for parameter lists or lookups.

Using VALUES in INSERT

You can use TVCs to insert multiple rows in a single statement:

INSERT INTO products (id, name, price)
VALUES
  (1, 'Pen', 1.20),
  (2, 'Pencil', 0.80),
  (3, 'Notebook', 3.50);

Combining with JOIN and IN clauses

Table constructors integrate naturally with joins and subqueries:

SELECT p.id, p.name, v.country
FROM products p
JOIN (VALUES
  ('UK'), ('US')
) AS v(country)
  ON p.origin = v.country;

Expanded forms in SQL:2023

SQL:2023 clarifies that table constructors can appear in FROM, WITH, and subquery expressions uniformly, and allows use of default column names when omitted. It also standardizes the optional TABLE keyword for readability.

SELECT *
FROM TABLE (VALUES (1, 'X'), (2, 'Y'));

New syntax updates in SQL:2023

The SQL:2023 revision adds multiple clarifications and new constructs across core syntax, JSON, temporal features, and analytic expressions. The main updates include:

Expanded MERGE capabilities

MERGE now supports multiple WHEN MATCHED and WHEN NOT MATCHED clauses with conditional logic. This makes upserts and complex synchronization operations more expressive.

MERGE INTO customers AS c
USING new_data AS n
ON (c.id = n.id)
WHEN MATCHED AND n.status = 'inactive' THEN
  UPDATE SET c.active = FALSE
WHEN MATCHED THEN
  UPDATE SET c.email = n.email
WHEN NOT MATCHED THEN
  INSERT (id, email, active) VALUES (n.id, n.email, TRUE);

Enhanced WINDOW clause

The WINDOW clause can now be reused across multiple analytic functions in the same query, avoiding repetition of PARTITION BY and ORDER BY definitions.

SELECT
  region,
  SUM(sales) OVER win AS total_sales,
  RANK() OVER win AS rank_in_region
FROM sales
WINDOW win AS (PARTITION BY region ORDER BY sales DESC);

Expanded INTERVAL and temporal precision

SQL:2023 refines temporal arithmetic, allowing microsecond precision and standardizing INTERVAL literals across engines.

SELECT CURRENT_TIMESTAMP + INTERVAL '3.5' SECOND AS in_future;

Standardized TRIM_ARRAY, CONTAINS_SUBSTRING, and MULTISET functions

New built-ins extend operations on strings, arrays, and multisets. These provide cross-engine consistency for manipulating structured values.

SELECT TRIM_ARRAY(ARRAY[1, NULL, 2, NULL]) AS compact,
       CONTAINS_SUBSTRING('This is SQL', 'SQL') AS found;

Row pattern recognition refinements

SQL:2023 builds on MATCH_RECOGNIZE (introduced in SQL:2016) to better support event stream analytics and state detection. This allows declarative sequence pattern queries.

SELECT *
FROM trades
MATCH_RECOGNIZE (
  PARTITION BY symbol
  ORDER BY ts
  MEASURES FIRST(A.ts) AS start_time, LAST(C.ts) AS end_time
  PATTERN (A B* C)
  DEFINE
    A AS price < 100,
    B AS price BETWEEN 100 AND 110,
    C AS price > 110
);

Summary of adoption

SQL remains a living standard. Each release brings the relational model closer to a unified language for both structured and semi structured data, capable of scaling from small embedded systems to distributed analytical clusters.