Expanding JSONB Into Typed Rows With jsonb_to_recordset

jsonb_array_elements hands you JSON values and leaves the casting to you: (e ->> 'qty')::int, (e ->> 'price')::numeric, repeated for every field in every query. PostgreSQL has a function that does the unpacking and the typing in one declaration, and it is the one I reach for when an array of objects needs to behave like a table. This article shows jsonb_to_recordset, its cousins jsonb_to_record, jsonb_populate_recordset and jsonb_each, what each returns, how they fail and how fast they are.

The basic shape

jsonb_to_recordset(array) is a set-returning function. Because it returns rows of an unknown type, you must declare the columns in an AS clause:

select o.id, x.*
from orders o,
     jsonb_to_recordset(o.doc -> 'items') as x(sku text, qty int, price numeric);

The comma is an implicit lateral join, so the function sees each order's own array. Output for the sample orders:

 id | sku | qty | price 
----+-----+-----+-------
  1 | A1  |   2 |   9.5
  1 | B2  |   1 |    20
  2 | A1  |   5 |   9.5

Each object became a row, and each declared column was cast from the JSON value to the SQL type. Order 3, with an empty array, produced no rows, so it is missing, as it was with jsonb_array_elements (use left join lateral ... on true to keep it). Now you can write ordinary SQL against x.qty and x.price with no casts in sight: sum(x.qty * x.price), joins to a products table on x.sku, window functions.

How the matching works

The function matches declared columns to object keys by exact name:

• Missing keys become NULL. I declared an extra column coupon text that no item has, and it came back NULL for every row. This is convenient for optional fields, and quiet about misspelled ones, so a typo in a column name gives you a column of NULLs and no error.

• Extra keys are ignored. Keys that you did not declare are dropped. Declare only what you need.

• Names are case-sensitive. For [{"a":1,"B":2}] the declaration x(a int, b int) returned b as NULL, and x(a int, "B" int) returned 2. The quoted identifier preserves case. The same applies to keys with spaces: x("first name" text) worked.

• Nested values can stay JSON. Declare a column as jsonb to keep an inner object or array as a document, and unpack it with a second call: x(a int, n jsonb) returned {"z": 1} for the first object and NULL for the one without n.

• Duplicate keys in the source were collapsed when the value was stored as jsonb, as in JSON versus JSONB in PostgreSQL, so there is one value per name.

How it fails

The function is strict about types, because it uses the same input conversion as a cast. If a value cannot be converted, the whole query fails:

jsonb_to_recordset('[{"a":1},{"a":"x"}]') as x(a int)
ERROR:  invalid input syntax for type integer: "x"

jsonb_to_recordset('[{"a":1.5}]') as x(a int)
ERROR:  invalid input syntax for type integer: "1.5"

Look at the second one. A JSON number with a fraction does not round to an integer; it errors. Declare numeric and cast afterward if rounding is what you want. The other source of errors is the shape: jsonb_to_recordset('[1,2]') raised argument of jsonb_to_recordset must be an array of objects, and passing an object instead of an array raised cannot call jsonb_to_recordset on a non-array.

There is no "skip bad rows" option in this function. In a pipeline that loads data from outside there are two ways around it. Declare every field text, cast in a second step with a guarded expression (case when qty ~ '^[0-9]+$' then qty::int end), and keep the rejects, or use JSON_TABLE, whose default ... on error clause does it in one step (using JSON_TABLE in PostgreSQL 17 to turn JSON into rows). Dates and times work with the same text formats a cast accepts: x(a date, t time) on {"a":"2026-10-01","t":"12:30"} gave 2026-10-01 | 12:30:00.

One object instead of many

jsonb_to_record is the same function for a single object. It returns one row:

select * from jsonb_to_record('{"a": 1, "b": "text", "c": [1,2], "d": {"e": 5}}')
  as x(a int, b text, c jsonb, d jsonb);

 a |  b   |   c    |    d     
---+------+--------+----------
 1 | text | [1, 2] | {"e": 5}

This is useful for splitting a single header object into columns, like the customer in an order: jsonb_to_record(doc -> 'customer') as c(id int, name text).

Using an existing type

Declaring the columns each time repeats yourself. If a table or composite type with the right shape exists, jsonb_populate_recordset takes its definition from the type of the first argument:

create type item as (sku text, qty int, price numeric);

select * from jsonb_populate_recordset(null::item,
  '[{"sku":"Z","qty":1,"extra":true},{"sku":"Y"}]');

 sku | qty | price 
-----+-----+-------
 Z   |   1 |      
 Y   |     |      

The null::item is only there to supply the type. Keys that the type lacks (extra) are ignored, and missing ones are NULL, the same rules as above. It works with any table's row type, and jsonb_populate_record(null::orders, '{"id": 9}') gave a record I could take .id from. The use I find most practical is loading JSON straight into a table: insert into order_items select * from jsonb_populate_recordset(null::order_items, :payload), where the table's own column types do the casting and a failing value rejects the whole insert inside a transaction.

Pairs instead of rows

When you do not know the keys, jsonb_each and jsonb_each_text return one row per key of an object:

select * from jsonb_each('{"a": 1, "b": {"c": 2}}');

returned a | 1 and b | {"c": 2}. The _text variant returns values as text. This is how you list the keys of a document, filter keys by type (where jsonb_typeof(value) = 'array' found tags and items in the sample order), or pivot a flexible document into key and value columns. jsonb_object_keys gives only the names.

Which one to choose

The cost question is the usual one, so I measured on 50,000 orders, 250,000 items, summing qty * price:

jsonb_to_recordset (typed columns)            82 ms
jsonb_array_elements with ->> and casts        87 ms
json_table (PostgreSQL 17+)                   145 ms

Each query returned the same sum, 33197290.00. The first run of each was slower and noisier, so take these as the second run. The first two are in the same range; jsonb_to_recordset is easier to read, with the field names and types declared once, and JSON_TABLE was the slowest in this test, though it is the only one with error handling and nested paths. The differences are small against the cost of detoasting and parsing the documents, which all three share. Choose on clarity and failure behavior.

A workable rule: jsonb_to_recordset when the fields are known, flat and well-behaved, jsonb_array_elements when I need the element itself or a single field, JSON_TABLE when I need nested paths or controlled errors, and a real table when I find myself writing this query in more than three places. If you can describe the element as a type, you can describe it as a table, and the JSON Schema generator is a quick way to see what that description looks like.

For arrays of scalars, querying arrays inside JSONB covers jsonb_array_elements_text. The whole workflow, from the first query on, is in working with JSONB in PostgreSQL from first query to index.

When to use JSON_TABLE or a real table instead

You need to survive bad data. One value of the wrong type fails the whole statement here. JSON_TABLE with default ... on error handles it, and a staging table of text columns lets you inspect rejects.

The path is nested. Order lines inside orders inside a customer wrapper need a path expression. JSON_TABLE with nested path does that in one clause, where jsonb_to_recordset needs another lateral call.

You use the result constantly. If every report unpacks items, they should be rows in a table with an index. Unnesting at query time scans and decodes each document every time, and a typed table does not.

Keys are not known in advance. For sparse, varying attributes, jsonb_each returns pairs and jsonb_path_query returns matches, and declaring columns is the wrong model.

The target is another system. If you are really exporting, COPY (select ... ) TO with the unpacked columns, or a tool like DuckDB reading the JSON directly (querying JSON files with DuckDB), may be simpler than building it in SQL.

The functions are documented in the PostgreSQL manual under JSON processing functions, and the SQL/JSON JSON_TABLE in the same chapter.