JSON_TABLE in PostgreSQL 17: JSON-to-relational queries become first-class — the property that bridges the JSON and SQL worlds

2 sources

PostgreSQL 17 introduces JSON_TABLE — a function that converts JSON documents into a relational table, enabling SQL queries against JSON data without first flattening it into columns. JSON-to-relational queries have been possible before with jsonb_to_recordset + LATERAL joins, but JSON_TABLE makes the pattern first-class SQL syntax that database optimizers can plan natively. The property bridges the JSON and SQL worlds: creators storing JSON documents can now run SQL queries against them as if they were relational tables, without explicit flattening. PG 17 also adds MERGE / RETURNING (single-statement conditional insert / update / delete with the affected row returned), logical replication failover (replica promotion without restart), and B-tree deduplication.

Why now

PG 17 ships JSON_TABLE — creators storing JSON can now run SQL queries against JSON data without explicit flattening.

Why it is worth publishing

Demo potential: side-by-side of the same JSON-to-relational query before and after PG 17 (jsonb_to_recordset vs JSON_TABLE).

Evidence basis

PostgreSQL 17 release notes + The Decoder weekly roundup

“PostgreSQL 17 ships JSON_TABLE — and the JSON-to-relational query pattern that previously required LATERAL joins is now first-class SQL syntax that database optimizers can plan natively.”

Angle

Frame JSON_TABLE as the property that bridges JSON and SQL worlds — creators storing JSON can now run SQL queries against JSON data without explicit flattening.

Format

Long-form explainer

Demo idea

Record a 10-minute explainer: 3 min intro on 'JSON-to-relational bridging' framing, 3 min on JSON_TABLE syntax, 2 min on a side-by-side query demo (jsonb_to_recordset vs JSON_TABLE), 2 min on the other PG 17 features (MERGE / RETURNING + logical replication failover).

Platform notes

Specific performance benchmark numbers beyond the captured summary are not extracted; confirm with creator-side benchmark before stating specific improvements. Specific migration steps beyond the captured summary are not extracted; confirm against the official upgrade guide before production migration.

Usable claims

PostgreSQL 17 reached GA — JSON_TABLE for JSON-to-relational queries, MERGE / RETURNING, logical replication failover, COPY performance improvements, B-tree deduplication.

Evidence pipeline

From the news

Breakdown

JSON_TABLE in PG 17 makes JSON-to-relational queries first-class SQL syntax (compared to the previous jsonb_to_recordset + LATERAL workaround). This explainer keeps the bridge property distinct from the workaround property, rather than treating 'JSON-to-relational' as a single capability.

Sources

Risks

Use The Decoder and IT之家 as media-type corroboration, but read the underlying vendor docs for any specific performance claim or pricing before stating it on the record. Verify specific capability claim against the underlying vendor docs and the actual license / pricing matrix before stating it on the record; do not paraphrase per-platform pricing or license terms into specific dollar figures or commercial-use clauses.
Release notes confirm the new features but specific migration steps are not in this pass. Verify specific capability claim against the underlying vendor docs and the actual license / pricing matrix before stating it on the record; do not paraphrase per-platform pricing or license terms into specific dollar figures or commercial-use clauses.

Demo ideas

Side-by-side JSON-to-relational query: jsonb_to_recordset + LATERAL vs JSON_TABLE.
Migration story: 'migrate a JSON-storing schema to use JSON_TABLE', measure query simplification.
PG 17 feature tour: JSON_TABLE + MERGE / RETURNING + logical replication failover + B-tree deduplication, with a query demo for each.