(This article accompanies the walk‑through on our YouTube channel — watch it if you want to see these execution plans live.)
TL;DR
-
jsonb_array_elements_text()turns a JSONB array of scalars into rows of text. -
jsonb_to_recordset()turns an array of JSON objects into typed relational columns (schema‑on‑read). - The one decision rule that saves your future self: if you’re joining, filtering, or aggregating on that array more than a couple of times a week, normalize it into a real table.
- All the SQL I talk about here is copy‑paste ready — no hand‑waving.
The problem: JSONB arrays and application‑side loops
You have an external API returning a payload, and instead of designing a structured schema for a fast‑moving integration, you dump the whole array into a jsonb column. It is a classic pattern.
The trouble starts when you need to query that nested data. The default move for many developers is to pull the raw JSONB field into the application, unmarshal it into an array of language‑native objects, and use a loop to filter or aggregate the entries.
This is a massive waste of network bandwidth and CPU cycles. Transferring megabytes of raw JSON text over the wire only to discard 90 percent of it in application memory is a bottleneck. Postgres can unpack and query these arrays directly in SQL before the data ever leaves the database.
jsonb_array_elements and jsonb_array_elements_text: Exploding scalar arrays
When you have a simple list of values — like a list of string tags or integer IDs inside a JSONB array — you have two tools: jsonb_array_elements() and jsonb_array_elements_text().
The difference between them is simple:
-
jsonb_array_elements(jsonb)returns each element as ajsonbvalue. If the element is a string, it appears with surrounding double quotes (like"postgres"). -
jsonb_array_elements_text(jsonb)returns each element directly astext, stripping the JSON quotation marks and saving you from an explicit cast.
Clarifier: jsonb_array_elements() without _text gives you JSON‑typed rows. A string would come out as "apple". If you’re going to compare with a text column or GROUP BY, you’d need an extra cast. Just use jsonb_array_elements_text() for scalar arrays and save yourself a step.
Here is a demo with a literal array:
SELECT * FROM jsonb_array_elements_text('["postgres", "reliability", "speed"]'::jsonb);
jsonb_array_elements_text
---------------------------
postgres
reliability
speed
(3 rows)
Now let’s apply this to a real table. Imagine an articles table where your marketing team stuffed tags into a single JSONB array:
CREATE TABLE articles (
id integer PRIMARY KEY,
title text,
tags jsonb
);
INSERT INTO articles VALUES
(1, 'Postgres Performance', '["sql", "performance", "db"]'::jsonb),
(2, 'Go Basics', '["go", "backend"]'::jsonb),
(3, 'Advanced SQL', '["sql", "db"]'::jsonb);
If you want to find your most popular tags across all articles, you do not need to pull the table into memory. Unpack the tags array, group the resulting rows, and run an aggregate count — all in one SQL statement:
SELECT
jsonb_array_elements_text(tags) AS tag,
count(*) AS article_count
FROM articles
GROUP BY tag
ORDER BY article_count DESC;
tag | article_count
-------------+---------------
sql | 2
db | 2
go | 1
performance | 1
backend | 1
(5 rows)
No Python loop, no Node.js memory pressure — the work stays in the database engine.
jsonb_to_recordset: Schema‑on‑read for arrays of objects
When your arrays contain complex objects instead of scalar strings, jsonb_to_recordset() is the tool you need. It maps the key‑value pairs inside your JSONB objects to typed relational columns on the fly.
Let’s look at an orders table storing a list of purchased line items inside a JSONB column:
CREATE TABLE orders (
id integer PRIMARY KEY,
line_items jsonb
);
INSERT INTO orders VALUES (101, '[
{"sku": "KB-99", "qty": 2, "price": 45.00},
{"sku": "MS-12", "qty": 1, "price": 15.50},
{"sku": "AD-01", "qty": 1}
]'::jsonb);
To unpack these objects, call jsonb_to_recordset() and specify your target schema in the AS clause.
Pay close attention to how Postgres handles missing and extra keys here. In the data above, the third item is missing a price key, and we will intentionally ignore any unexpected fields like warehouse.
SELECT * FROM jsonb_to_recordset(
'[
{"sku": "KB-99", "qty": 2, "price": 45.00, "warehouse": "east"},
{"sku": "MS-12", "qty": 1, "price": 15.50, "warehouse": "west"},
{"sku": "AD-01", "qty": 1}
]'::jsonb
) AS items(sku text, qty int, price numeric);
sku | qty | price
-------+-----+-------
KB-99 | 2 | 45.00
MS-12 | 1 | 15.50
AD-01 | 1 | [NULL]
(3 rows)
The database maps keys to your specified columns. If a key is missing in the JSON object (like price on the third item), it returns a standard SQL NULL. If a key is in the JSON but omitted from your AS definition (like warehouse), Postgres silently ignores it.
Two patterns you’ll actually use
In real‑world development, you rarely extract standalone literal arrays. You need to tie those exploded rows back to their parent records.
1. The LATERAL join (preserving context)
To cleanly link each exploded row to its parent table record, use a LATERAL join. It acts like a foreach loop inside your SQL, evaluating the set‑returning function for every parent row. (A comma in the FROM clause implicitly does a CROSS JOIN LATERAL, but writing LATERAL explicitly makes the intent clear for the next developer.)
SELECT
o.id AS order_id,
li.sku,
li.qty,
li.price
FROM orders o,
LATERAL jsonb_to_recordset(o.line_items)
AS li(sku text, qty int, price numeric);
order_id | sku | qty | price
----------+-------+-----+-------
101 | KB-99 | 2 | 45.00
101 | MS-12 | 1 | 15.50
101 | AD-01 | 1 | [NULL]
(3 rows)
2. Live aggregation over JSONB arrays
Because these functions return genuine relational rows, you can plug them directly into standard Postgres math functions. To compute the total value of each order, coalesce any missing values and sum the products:
SELECT
o.id AS order_id,
SUM(COALESCE(li.qty, 0) * COALESCE(li.price, 0.00)) 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;
order_id | order_total
----------+-------------
101 | 105.50
(1 row)
No subqueries or CTEs needed — the aggregation runs directly over the in‑memory rows produced by the set‑returning function.
Edge cases that bite
Using these functions is straightforward until you hit messy, production‑grade JSON payloads. There are four behavioral edge cases that can break your queries if you do not account for them.
Empty arrays yield zero rows
If you use a LATERAL join on an empty JSONB array ([]), the set‑returning function returns zero rows. Because a LATERAL join behaves like an inner join by default, the entire parent row is excluded from your query results. To keep the parent row even when its array column is empty or null, use a LEFT JOIN LATERAL ... ON true.
NULL inputs
Passing a SQL NULL value into jsonb_array_elements or jsonb_to_recordset returns zero rows — no error, but the parent row disappears if you used an inner join. Again, LEFT JOIN LATERAL is your friend.
Non‑array inputs throw database errors
If your column accidentally contains a JSON object ({"sku": "KB-99"}) instead of an array of objects ([{"sku": "KB-99"}]), Postgres will throw an error immediately:
SELECT * FROM jsonb_array_elements('{"sku": "KB-99"}'::jsonb);
-- ERROR: cannot extract elements from an object
You can guard against this with a WHERE clause that checks jsonb_typeof(), but a better long‑term solution is to add a CHECK constraint so non‑array values never land in the column in the first place.
No auto‑flattening of nested structures
These functions only inspect the top‑level array. If your objects contain nested arrays (e.g., a line item that itself has an array of tax identifiers), you will need to stack multiple LATERAL joins to drill down to the nested elements. At that point, consider whether your data should have been normalised into proper tables.
jsonb_populate_recordset: The typed‑row alternative
As your database grows, you might find yourself writing the same long column lists (AS (sku text, qty int, price numeric)) over and over again.
If you already have a composite type or an existing table that matches the structure of your JSONB object, you can use jsonb_populate_recordset() instead.
-- Establish a database-wide type
CREATE TYPE order_item_type AS (
sku text,
qty int,
price numeric
);
-- Query using the pre-defined target type
SELECT * FROM jsonb_populate_recordset(
NULL::order_item_type,
'[{"sku": "KB-99",
'[{"sku": "KB-99", "qty": 2, "price": 45.00}, {"sku": "MS-12", "qty": 1, "price": 15.50}]'::jsonb
);
The rows come back typed exactly how your application expects them — same column names, same data types. If you’re hitting the same JSONB schema across multiple queries, defining a composite type upfront saves repetition and catches type mismatches at the database level.
Real performance monitoring – when JSONB patterns start to hurt
The SQL you’ve just learned is incredibly powerful for ad‑hoc analysis, quick API responses, and data imports that rarely change. But once you start running these set‑returning functions inside daily reports, cron jobs, or high‑throughput API endpoints, the overhead becomes measurable.
The rule from the TL;DR is not a hard line; it’s a signal. The moment you find yourself writing jsonb_to_recordset inside a function that runs every few minutes, it’s time to stop and ask: Could a properly indexed relational table do this faster? Often the answer is yes — and the migration is just a INSERT INTO … SELECT FROM jsonb_to_recordset away.
Monitoring those queries is where a tool like MyDBA saves you from guesswork. Instead of manually checking EXPLAIN ANALYZE for every LATERAL join, MyDBA tracks how often those patterns run, how much CPU they consume, and whether a simple index on a normalized table would cut execution time by orders of magnitude. You see the real cost of your JSONB strategy in production, not just on a laptop test run.
When you finally normalize that array, MyDBA’s health‑check feature also watches for missing indexes on the new table, so the migration doesn’t accidentally degrade performance. It turns a gut‑feel decision into a data‑backed one.
pgdba Editorial builds MyDBA, a Postgres monitoring and health-check tool — https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-can-explode-a-jsonb-array-into-queryable-rows-directly-in-sql
Start monitoring your Postgres queries for free and catch the JSONB bottlenecks before your users do: https://mydba.dev/?utm_source=devto&utm_medium=platform&utm_campaign=postgres-can-explode-a-jsonb-array-into-queryable-rows-directly-in-sql.
Top comments (0)