Postgres jsonb_array_elements: Turn JSON arrays into queryable rows inside SQL

The problem: JSON payloads jammed into a column

We have all seen it in production systems: an upstream API sends down a nested payload, and instead of designing a proper relational schema, someone decides to dump the raw JSON straight into a single jsonb column. This approach works fine for the first week. Then the application needs to run analytical queries, generate reports, or join nested data to other tables.

If your first instinct is to pull the raw JSONB blobs into your application layer and write nested loops in Ruby, Python, or Go to parse elements, you are wasting database power and network bandwidth. You do not need to pull JSON arrays out of the database to manipulate them. PostgreSQL can unroll them for you, directly in SQL, with a handful of functions that treat a JSON array as a set of rows. If you watched the companion video, you’ve seen the basics; here we dig into the real‑world edges and the decision of when not to explode at all.

jsonb_array_elements vs jsonb_array_elements_text

The primary tool is jsonb_array_elements. It takes a top‑level JSON array and returns a set of jsonb values—one row per element.

SELECT * FROM jsonb_array_elements('[{"a":1}, {"a":2}, {"a":3}]'::jsonb);

 jsonb
----------
 {"a": 1}
 {"a": 2}
 {"a": 3}

If your array holds plain scalars (strings, numbers, booleans) and you just want text, there’s jsonb_array_elements_text:

SELECT * FROM jsonb_array_elements_text('["apple", "banana", "cherry"]'::jsonb);

 text
--------
 apple
 banana
 cherry

Heads up: the _text variant fails with ERROR: cannot call jsonb_array_elements_text on a non-string if it hits an object or array element. Stick with the base function for mixed or object payloads, then use the -> and ->> operators to pull out fields. -> returns jsonb (an object or array); ->> returns text. Cast the text to a real type when you need math or ordering.

Preserving array order often matters. Just bolt on WITH ORDINALITY:

SELECT elem, ordinality
FROM jsonb_array_elements('[{"a":1}, {"a":2}]'::jsonb) WITH ORDINALITY AS t(elem, idx);

That gives you a 1‑based index alongside each row. Handy when sequence is positional (line‑items, event steps).

Exploding a column across a table

Take a real articles table that squashes tags into a JSON array:

CREATE TABLE articles (
    id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title   text,
    tags    jsonb
);

INSERT INTO articles VALUES
(1, 'Postgres Tips', '["postgres", "sql", "jsonb"]'),
(2, 'Indexing War Stories', '["postgres", "btree", "gin"]');

To roll the tags out so that every tag becomes a row tied to its article, you put the function in the FROM clause:

SELECT id, jsonb_array_elements_text(tags) AS tag
FROM articles;

 id |   tag
----+----------
  1 | postgres
  1 | sql
  1 | jsonb
  2 | postgres
  2 | btree
  2 | gin

Now you’re holding a virtual many‑to‑many table. Filter with WHERE tag = 'postgres', count with GROUP BY tag, or join to a real tags catalog. The explosion happens on the fly, no schema change needed.

But when the array isn’t scalars—say each element is an object with known keys—you’ll want something that gives you typed columns directly. That’s jsonb_to_recordset.

jsonb_to_recordset: schema-on-read for arrays of objects

jsonb_to_recordset expands an array of JSON objects into a set of rows whose columns you define in the AS clause. Missing keys become NULL; extra keys are silently ignored. It’s pure schema-on-read.

Worked example with an orders table:

CREATE TABLE orders (
    id          integer PRIMARY KEY,
    line_items  jsonb
);

INSERT INTO orders VALUES
(1, '[{"sku":"SKU1", "qty":2, "price":9.99}, {"sku":"SKU2", "qty":1, "price":14.50}]'),
(2, '[{"sku":"SKU3", "qty":5, "price":1.25}]');

Take a single literal array to see the function’s output:

SELECT *
FROM jsonb_to_recordset(
    '[{"sku":"SKU1", "qty":2, "price":9.99}, {"sku":"SKU2", "qty":1, "price":14.50}]'::jsonb
) AS (sku text, qty int, price numeric);

 sku  | qty | price
------+-----+-------
 SKU1 |   2 |  9.99
 SKU2 |   1 | 14.50

Test the edge: a missing key.

SELECT *
FROM jsonb_to_recordset('[{"sku":"SKU3", "price":5.00}]'::jsonb)
    AS (sku text, qty int, price numeric);

 sku  | qty | price
------+-----+-------
 SKU3 |     |  5.00

qty drops in as NULL—no error. Likewise, if an object carries a key you didn’t list (say "color":"red"), the function ignores it.

The LATERAL join pattern to keep the parent ID

To run jsonb_to_recordset across every order in the table, you need the function to see o.line_items for each row. Standard SQL requires LATERAL for that kind of correlated reference inside FROM:

SELECT o.id, li.*
FROM orders o,
     LATERAL jsonb_to_recordset(o.line_items) AS li(sku text, qty int, price numeric);

 id | sku  | qty | price
----+------+-----+-------
  1 | SKU1 |   2 |  9.99
  1 | SKU2 |   1 | 14.50
  2 | SKU3 |   5 |  1.25

The LATERAL keyword (explicit or inferred via a comma join in recent PG versions) is what keeps the parent row alive next to each exploded child. Without it you’d get an error trying to reference o.line_items inside the function call. If you ever need to keep orders that have an empty or NULL array, write LEFT JOIN LATERAL instead—otherwise the orders disappear from the result.

Aggregating across exploded rows

Once you’ve exploded, it’s just SQL. That’s the whole point. Get an order total:

SELECT o.id, SUM(li.qty * li.price) AS order_total
FROM orders o,
     LATERAL jsonb_to_recordset(o.line_items) AS li(sku text, qty int, price numeric)
GROUP BY o.id;

Filter for big‑quantity items only:

SELECT o.id, li.sku, li.qty
FROM orders o,
     LATERAL jsonb_to_recordset(o.line_items) AS li(sku text, qty int, price numeric)
WHERE li.qty > 1;

Join to a real products table to flesh out the SKU:

SELECT o.id, p.product_name, li.qty, li.price
FROM orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.line_items) AS li(sku text, qty int, price numeric)
JOIN products p ON p.sku = li.sku
WHERE li.qty > 1;

All these operations run against the in‑memory set that the explosion produces. No temporary tables, no application loops.

Edge cases that bite people

After cleaning up enough JSONB messes, you learn to expect these.

NULL array, empty array, or missing key. If line_items is SQL NULL, jsonb_to_recordset returns no rows, and an un‑lateraled join drops the parent row. An empty array '[]' also yields zero rows. That’s usually what you want, but be explicit with LEFT JOIN LATERAL when you need the parent to survive.

The column doesn’t hold an array. If you pass a JSON object '{"sku":"X"}' to jsonb_to_recordset, PostgreSQL raises “cannot call jsonb_to_recordset on a non‑array.” Real tables sometimes contain a mix of single objects and arrays. Wrap the reference in a CASE:

CASE WHEN jsonb_typeof(line_items) = 'array'
     THEN line_items
     ELSE jsonb_build_array(line_items) END

Array of non‑objects or type mismatches. jsonb_to_recordset demands an array of objects. Give it '["a","b"]' and you’ll get “cannot call jsonb_to_recordset on a scalar.” For scalar arrays stick with jsonb_array_elements_text. Even when the elements are objects, your typed column list makes no promises about the actual JSON type. Declared qty int but the object has "qty": "5"? PostgreSQL coerces the string to integer if it looks like a number; "qty": "five" will blow up with an invalid syntax error. If your data is dirty, you’ll need a safe‑cast function or an intermediate jsonb_array_elements step with manual extraction.

Order without WITH ORDINALITY. By default the rows follow array order, but SQL itself requires an ORDER BY clause for guaranteed output order. For jsonb_to_recordset there’s no direct WITH ORDINALITY hook. You can hack it by exploding the array with jsonb_array_elements and WITH ORDINALITY first, then extracting fields in a subquery:

SELECT o.id, elem->>'sku' AS sku, (elem->>'qty')::int AS qty, (elem->>'price')::numeric AS price, idx
FROM orders o,
     LATERAL jsonb_array_elements(o.line_items) WITH ORDINALITY AS t(elem, idx);

It’s clumsier, but preserves position reliably.

jsonb_array_elements vs jsonb_to_recordset vs jsonb_populate_recordset: quick decision table

Scenario	Function to use
Array of scalars (strings, numbers)	jsonb_array_elements_text (if all textable) or jsonb_array_elements with ->>
Array of objects, schema known at query time	jsonb_to_recordset with explicit AS (col type, ...)
Array of objects, you already have a composite type defined	jsonb_populate_recordset(null::my_type, jsonb_array)
Nested arrays (array inside array)	Outer explode first, then another lateral jsonb_array_elements on the inner element—no built‑in recursion
Keys vary per row or schema is dynamic	Stick to jsonb_array_elements and use ->/->> on each element; you’ll write more extraction boilerplate

jsonb_populate_recordset is the sibling that accepts a base row type instead of an inline column list. It’s nicest when you’ve created a composite type (say CREATE TYPE line_item AS (sku text, qty int, price numeric)) and want to reuse it across queries. Same behavior: missing keys → NULL, extras ignored.

When to actually normalize into real tables

This is the opinionated payoff. I’ve watched teams leave heavily‑queried arrays inside JSONB for a year because “the function works.” It does work, until the dashboard starts timing out and you realize you’re decoding fifty thousand JSON objects on every refresh.

Here’s the heuristic I use:

If you’re aggregating or joining the exploded array in more than a couple hot‑path queries per day, or if you need to index the individual elements or enforce a foreign key, stop exploding on‑the‑fly and materialize a real child table.

Concrete decision factors:

• Query frequency. A few ad‑hoc reports? JSONB is fine. A dashboard that runs every thirty seconds and aggregates line_items across a million orders? The CPU spent re‑parsing JSON will make you sad. A side table refreshed by a trigger or a batch job, with plain B‑tree indexes on (order_id, sku), will smoke the exploded approach.
• Indexing needs. GIN indexes on the jsonb column accelerate containment queries (@>, ?), but they don’t index the exploded rows themselves. You can slap an expression index on (line_items -> 'sku') to find orders that contain a given SKU quickly, but that still operates at the order grain. If you need a per‑line‑item index—say to filter by qty or price

—you need a real table underneath. – Referential integrity. You can’t declare a REFERENCES constraint against a key buried inside a JSON array. The moment you need to guarantee that every line_item.sku exists in a products table, it’s time to pull the array out into a proper child table with a foreign key. – Write patterns. If the array is replaced wholesale on every update (UPDATE orders SET line_items = '[...new array...]'), the exploded view is cheap to maintain because you’re not dealing with incremental sync. But if your application pushes individual elements in and out—jsonb_insert, jsonb_set, jsonb_delete—you’re already performing surgery inside blobs. A real junction table with INSERT/DELETE semantics will be clearer and probably faster. – Data volume per row. An array of three line items is trivial. An array of 8,000 event log records inside a single jsonb column for every user session is a sign that someone hates you. Exploding it at query time will allocate enormous temporary memory and thrash your CPU. Materialize it or, better, redesign the ingestion pipeline so that each event becomes a row immediately. I’m not saying throw away jsonb_array_elements. I use it regularly for one‑off analysis, debug queries, and light‑duty reporting. But I treat it as a ladder to data clarity, not a permanent floor plan. Once the queries grow serious, normalize. The database is happier when it can breathe through B‑trees instead of decoding JSON over and over. ## Explosion is the first step; real tables are the destination PostgreSQL’s JSONB expansion functions give you a seamless bridge between the messy, nested payloads that arrive from external systems and the relational query engine you actually want to use. jsonb_array_elements turns arrays into rows in a single function call. jsonb_to_recordset adds column‑shaped structure on top, letting you write SUM(qty * price) as if you’d designed the schema properly from day one. The LATERAL pattern keeps parent rows connected, and you can layer on filtering, grouping, and joins just as you would with native tables. But the true win isn’t learning the function syntax—it’s knowing when to stop using it. Exploding JSON on‑the‑fly is a power tool for exploration, ad‑hoc analysis, and low‑traffic reporting paths. When query volume climbs, or integrity constraints start to matter, or you catch yourself writing the same lateral explosion in five different views, that’s the database telling you it’s time to graduate. Write a migration, set up a trigger or an ETL step to keep a real child table in sync, and let the query planner do what it does best with normal rows and proper indexes. JSONB gives you flexibility; normalization gives you speed and safety. Use both, in sequence, and you’ll never dread a nested payload landing in your database again.