Using JSON_TABLE in PostgreSQL 17 to Turn JSON Into Rows

If you have ever written jsonb_array_elements inside jsonb_array_elements with casts on every field, you know the problem JSON_TABLE solves. PostgreSQL 17 added it, following the SQL standard, so a nested document can become a flat set of typed rows with one clause. It does the work of the older functions and adds things they could not do: nested paths, row numbers, existence tests and controlled error handling. This article works through it on an API-style document with users, orders and order lines, and measures it against the alternatives.

The shape of the call

JSON_TABLE is used in the FROM clause. It takes the document, a row path that selects the rows, and a COLUMNS list:

select jt.*
from api_dump d,
     json_table(d.body, '$' columns (
       user_id     int  path '$.user.id',
       name        text path '$.user.name',
       city        text path '$.user.address.city' default 'unknown' on empty,
       first_email text path '$.user.emails[0]'
     )) jt
order by 1;

 user_id | name  |  city   |  first_email   
---------+-------+---------+----------------
       7 | Ada   | Lyon    | [email protected]
       8 | Grace | unknown | 

The row path '$' produced one row per document. Each column has a type and a path relative to the row. User 8 has "address": null, so $.user.address.city found nothing, and the default 'unknown' on empty clause supplied a value. The email column is NULL for Grace because her array is empty. The document is read with the SQL/JSON path language that PostgreSQL has had since 12, which is the language described in extracting nested JSONB values with path operators.

One row per array element

Change the row path to point at an array and you get a row per element:

json_table(d.body, '$.orders[*]' columns (
  n         for ordinality,
  id        int     path '$.id',
  has_lines boolean exists path '$.lines[0]',
  lines     jsonb   format json path '$.lines',
  line_count int    path '$.lines.size()'
))

For user 7 it returned two rows: order 100 with has_lines = t and two lines, and order 101 with has_lines = f and an empty lines. Three column kinds appear here that the older functions lack:

• for ordinality numbers the rows from 1, which gives you the position of each element without with ordinality and a separate alias.

• exists path returns a boolean: does the path produce anything. It is the @? operator as a column.

• format json keeps the value as JSON, for a nested object or array you want to pass on or unpack later.

You can call jsonpath methods inside a column path, as $.lines.size() did above, to compute the count with no further SQL.

Nested arrays: NESTED PATH

The reason to learn JSON_TABLE is NESTED PATH. It descends into an inner array and joins its rows to the parent:

select jt.*
from api_dump d,
     json_table(d.body, '$' columns (
       user_id int path '$.user.id',
       nested path '$.orders[*]' columns (
         order_id int     path '$.id',
         total    numeric path '$.total',
         nested path '$.lines[*]' columns (
           sku text path '$.sku',
           qty int  path '$.qty'
         )
       )
     )) jt
order by 1, 2, 4;

 user_id | order_id | total | sku | qty 
---------+----------+-------+-----+-----
       7 |      100 | 19.90 | A1  |   2
       7 |      100 | 19.90 | B2  |   1
       7 |      101 |  5.00 |     |    
       8 |          |       |     |    

This is the entire three-level flattening in one statement. Look at the rows that have empty columns. Order 101 has no lines, and it still appears, with sku and qty empty. User 8 has no orders, and the user still appears with empty order columns. NESTED PATH behaves like a left join, so a parent without children is not lost. That differs from the cross join over jsonb_array_elements in querying arrays inside JSONB, where an empty array removes the parent and you have to ask for a left join. The total came out as 19.90 because the document stores it as a string and the column type is numeric; the cast happens in the column definition.

Siblings are not a cross join

If you put two NESTED PATH clauses next to each other, you might expect every email combined with every order. That is not what happens:

json_table(d.body, '$' columns (
  user_id int path '$.user.id',
  nested path '$.user.emails[*]' columns (email text path '$'),
  nested path '$.orders[*]'      columns (order_id int path '$.id')
))

For user 7 the result was two rows with an email and no order, then two rows with an order and no email, and one row for user 8 with everything empty. The sibling paths are joined as a union: the rows of each branch appear separately, padded with NULLs, so there is no multiplication of rows. That is usually what you want when the two lists are unrelated, and it is easy to misread as a bug. If you do want a cross product, make two JSON_TABLE calls and join them yourself.

Controlling errors and empties

The older unpacking functions fail the whole query on a bad value. JSON_TABLE lets you decide per column, which is the best reason to use it for data from outside. The defaults: a value that cannot be converted gives NULL, and a missing path gives NULL. On the input [{"v":"12"},{"v":"abc"},{"w":1}] with a column v int path '$.v':

default (null on error, null on empty)      12, NULL, NULL
v int path '$.v' error on error              ERROR:  invalid input syntax for type integer: "abc"
v int path '$.v' default -1 on error         12, -1, NULL
v int path '$.v' error on empty              ERROR:  no SQL/JSON item found for specified path of column "v"

Pick the clause to fit the job. For a pipeline that must not quietly drop data, error on error and error on empty stop it at the first problem and tell you which column failed. For an import that should keep going, default supplies a marker you can search for later. The error message for the type problem is PostgreSQL's usual one; the error for a missing item names the column but not the row, so combine for ordinality with a narrower query when you have to find it.

Two more details. A strict path in the row expression raises on a missing key like any jsonpath: json_table('{"a":{"b":1}}', 'strict $.zzz' columns (...)) error on error raised JSON object does not contain key "zzz". And for text columns holding JSON, with wrapper wraps matches in an array ([[1, 2]] for a value [1,2] and [3] for 3), omit quotes strips the quotes of a string (x), and keep quotes leaves them ("x").

Related functions: JSON_VALUE, JSON_QUERY, JSON_EXISTS

PostgreSQL 17 added three scalar relatives that share the path language. JSON_VALUE(doc, '$.a.b' returning int) returned 5 for {"a": {"b": 5}}, and it returns a single scalar; if the path matches an array it raises JSON path expression in JSON_VALUE must return single scalar item. JSON_QUERY returns a JSON fragment ([1, 2] for $.a.b over {"a": {"b": [1,2]}}), and JSON_EXISTS returns a boolean. They are the standard-syntax equivalents of #>>, #> and @?, with the same on error and on empty clauses.

How fast is it

I ran the same sum, qty * price over 250,000 items in 50,000 orders (five items each), three ways, the warm runs:

jsonb_to_recordset                          82 ms
jsonb_array_elements with ->> and casts      87 ms
json_table                                  145 ms

JSON_TABLE was the slowest by a factor of 1.7, which is not surprising for a general mechanism, because it evaluates a path expression per column. The difference is a fraction of a second on a quarter of a million items. If you run this inside a hot request path or a report over many millions of items, measure your own data; otherwise the clarity and error control are the better trade. See expanding JSONB into rows with jsonb_to_recordset for the other two methods in detail.

You cannot index JSON_TABLE's output because it is computed. If a report is slow, the fix is the same as for any unpacking query: store the elements as rows, or materialize the result, as described in working with JSONB in PostgreSQL from first query to index.

When JSON_TABLE is more than you need

A flat array of simple objects. jsonb_to_recordset says the same thing in fewer words and a bit faster.

A single value. doc #>> '{a,b}' or JSON_VALUE is shorter than a one-column table.

Support for older servers. JSON_TABLE needs PostgreSQL 17. If you must run on 16 or earlier, or share SQL with a managed service that has not upgraded, use the older functions. Oracle, MySQL and MariaDB have JSON_TABLE too, with differences in syntax details, so SQL that works on one needs checking on the others; see JSON_TABLE to turn MySQL JSON into rows for the MySQL form.

Data you query repeatedly. Unpacking on the fly is fine for ad hoc work and migrations. Queries that run all day belong on real tables.

The syntax is documented in the PostgreSQL manual under JSON_TABLE, and the underlying path language under SQL/JSON path expressions. A tool to try a path expression before you put it in a column is the JSONPath tester on this site, which runs in your browser.