The road to new SQL/JSON features
summary
Postgres now implements a near-complete SQL/JSON suite landed across several late commit Fests and included in Postgres 17, notably JSON_TABLE with nested support and the JSONB storage format added years earlier for indexing. The work uses soft errors to avoid expensive savepoint-based parsing, adds JSON constructors and aggregates (json, json_array, json_object, json_arrayagg, json_objectagg), JSON functions (json_parse, json_serialize), and SQL/JSON path, query, value and exists operators; some early patches were reverted and reworked for correctness and performance.
AI-written summary · about
chapters (14)
- 0:00Introduction to SQL/JSON features
- 1:33Early JSON implementation in PostgreSQL
- 4:52Transition to JSONB and indexing
- 10:19SQL/JSON standard adoption challenges
- 16:46Soft errors and JSON parsing improvements
- 18:13JSON constructors in PostgreSQL 16
- 28:00JSON array and object aggregates
- 31:13JSON predicate in PostgreSQL 16
- 33:23PostgreSQL 17 SQL/JSON updates
- 35:56JSON value constructors
- 38:25JSON query functions in PostgreSQL 17
- 41:22JSONPath and nested queries
- 44:13JSON table with nested clauses
- 45:59Summary and future outlook
related talks
35mLeveraging PostgreSQL 17's JSONB for Enhanced Data Modeling
42mNoSQL as Not Only SQL
45mHow Well Do Relational Database Engines Support JSON?
49mShould I use JSON in PostgreSQL?
50mUsing JSON with Postgres 9 3
05mPostgreSQL JSON JSONB Making the Right Choice for Your Application
23mEven JSONB In Postgres Needs Schemas
33mSchemaless SQL The Best of Both Worlds