SQL JSON Support Added in Recent Versions of PostgreSQL

Before these additions, working with JSON in PostgreSQL meant ->, ->>, @>, jsonb_path_query and a handful of set-returning functions such as jsonb_array_elements. Powerful, and not the SQL standard. Versions 16 and 17 added the standard SQL/JSON constructors, predicates and query functions, culminating in JSON_TABLE. They do not replace the older tools, and they make some tasks shorter and safer, especially loading documents into tables. This article walks through each addition by version, with examples, and says when to use which.

Constructors (16)

Version 16 added standard ways to build JSON in SQL:

SELECT JSON_OBJECT('sku' VALUE sku, 'qty' VALUE qty) FROM stock LIMIT 1;
SELECT JSON_ARRAY(1, 'two', NULL NULL ON NULL);
SELECT JSON_OBJECTAGG(sku VALUE qty) FROM stock;
SELECT JSON_ARRAYAGG(sku ORDER BY sku) FROM stock;

JSON_OBJECT and JSON_ARRAY build values with explicit handling of nulls (NULL ON NULL or ABSENT ON NULL) and unique keys (WITH UNIQUE KEYS). The aggregate forms collect rows into one document. They overlap with jsonb_build_object, json_agg and friends; the new forms are standard, portable to other databases that support SQL/JSON and give you the null and key handling options in the syntax. Version 17 adds JSON(), JSON_SCALAR() and JSON_SERIALIZE() to convert between text, JSON and scalar values.

IS JSON (16)

The predicate tests whether a value is valid JSON of a given kind:

SELECT '{"a": 1}' IS JSON OBJECT, '[1,2]' IS JSON ARRAY, '"x"' IS JSON SCALAR, 'nope' IS JSON;
ALTER TABLE event ADD CONSTRAINT payload_is_object CHECK (payload IS JSON OBJECT);

On a text column, it checks syntax; on jsonb, it checks type. Added as a check constraint it enforces that stored documents are objects, or that a field with an expected shape is valid. WITH UNIQUE KEYS rejects duplicate keys. For staging tables that hold raw payloads as text, IS JSON is the first filter: select the rows where it is false to find bad input before parsing.

Query functions (17)

Three functions evaluate a jsonpath expression against a document, each returning something different:

SELECT JSON_EXISTS(doc, '$.items[*] ? (@.qty > 10)') FROM orders_raw;
SELECT JSON_VALUE(doc, '$.customer.id' RETURNING int) FROM orders_raw;
SELECT JSON_QUERY(doc, '$.items' WITH WRAPPER) FROM orders_raw;

JSON_EXISTS returns a boolean for whether the path matches. JSON_VALUE extracts a single scalar and can cast it with RETURNING. JSON_QUERY extracts an object or array and can wrap multiple results in an array. All three take ON ERROR and ON EMPTY clauses, so you choose per call between NULL, ERROR, DEFAULT value and other behaviors, for example JSON_VALUE(doc, '$.qty' RETURNING int DEFAULT 0 ON EMPTY NULL ON ERROR). The explicit handling is a real improvement over ->> followed by a cast that fails on bad data. They use SQL/JSON path, the same language as jsonb_path_query.

JSON_TABLE (17)

The big one. JSON_TABLE turns a document into a table, in the FROM clause:

SELECT o.id, jt.*
FROM orders_raw o,
     JSON_TABLE(o.doc, '$.items[*]' COLUMNS (
         line  FOR ORDINALITY,
         sku   text    PATH '$.sku',
         qty   int     PATH '$.qty' DEFAULT 1 ON EMPTY,
         price numeric PATH '$.price',
         NESTED PATH '$.tags[*]' COLUMNS (tag text PATH '$')
     )) AS jt;

The row path ($.items[*]) says what makes a row; each column has a type and a path; FOR ORDINALITY numbers the rows; NESTED PATH expands an inner array, repeating the outer row for each element. Compare with the older way: jsonb_array_elements(doc->'items') AS item, then item->>'sku', (item->>'qty')::int and so on, a chain that is harder to read and fails on the first cast error. For loading API payloads and event documents into relational tables, JSON_TABLE replaces a page of code with a declaration. Because it works with the JSON_VALUE family's error options, you can say what to do with bad data at each column.

Version 18 additions

As described in its release notes, version 18 continued the work, and you should check them for details such as additional JSON_TABLE capabilities and new functions. A habit worth keeping: read the JSON section of each release's notes, since this area has seen steady additions.

Indexes and performance

The new functions are evaluation tools, and they do not by themselves use indexes. GIN indexes accelerate jsonb operators such as @> (containment), ? (key exists) and @?/@@ (jsonpath), so a query that must be fast over a large table should use those operators in the WHERE clause, or an expression index on the extracted value: CREATE INDEX ON orders_raw (((doc->>'customer_id')::int)). JSON_VALUE(doc, '$.customer.id' RETURNING int) can also be indexed as an expression, but the planner matches expressions by their exact text, so use the same form in the index and in queries. For a column you filter on often, promote it to a real column with a generated expression and index that. The patterns for staging raw documents and shredding them are in using COPY with STDIN from an application and PostgreSQL versus MongoDB for flexible data.

Using them in migrations and checks

The new predicates also help with data quality work. During a migration from a document store or an API archive, load every payload into a text column, count the rows where IS JSON is false, and look at a sample of them before converting. After conversion, JSON_EXISTS can verify that required paths are present in every document, which turns an informal expectation into a query you can run and put in a test: SELECT count(*) FROM orders_raw WHERE NOT JSON_EXISTS(doc, '$.customer.id'). For reports over documents of varying shape, JSON_VALUE with DEFAULT ... ON EMPTY gives a stable numeric column even when some documents lack the field, which keeps aggregates from failing on a single odd record. Keep these checks in the load job so that bad documents are counted and set aside, and not discovered by a dashboard that suddenly shows a gap.

Compatibility

These functions exist only in the versions that added them. A managed service on 15 cannot run JSON_TABLE, and a client library that pre-parses SQL may not know the syntax (most pass it through). If you must support several versions, keep a fallback with jsonb functions or gate by the server version. When you move code between databases, note that the SQL/JSON standard is implemented with differences by each vendor; the syntax is similar, with variations in options and behavior.

When jsonb operators are still the better tool

For a quick access to one field, doc->>'name' is shorter than JSON_VALUE(doc, '$.name'). For containment tests that use a GIN index, doc @> '{"status":"open"}' is the tool. For building documents in application-facing queries, jsonb_build_object is compact. Use the SQL/JSON functions when you want explicit error handling, standard syntax or JSON_TABLE to shred documents into rows. The two sets mix freely in one query.

What to do next

If you are on 17 or later, rewrite one query that unpacks a JSON array with jsonb_array_elements using JSON_TABLE, and compare the readability and the plan. Add IS JSON checks to staging tables. The manual documents the syntax in JSON functions and operators and the types in JSON types. The hub is what changed in recent PostgreSQL releases.