Extracting Nested Data: jsonb_to_recordset()

Now we’re moving on to more advanced scenarios with JSONB data — extracting nested data and transforming it into table rows. You might ask, why bother? It’s simple! Imagine you get a JSON object with a list of purchases and you’re asked to calculate the total sum of all purchases or show them in a table for a report. We’re going to break this down right now!

Why can’t you just work with JSON as plain text or a structure? Let’s look at a scenario. In a lot of real-world apps, data is stored as JSON arrays:

[
  { "id": 1, "product_name": "Laptop", "price": 1200 },
  { "id": 2, "product_name": "Smartphone", "price": 800 },
  { "id": 3, "product_name": "Tablet", "price": 400 }
]

This is handy, but when you need to analyze the data, you often want to turn the array into a table so you can filter, sort, and aggregate. Picture this: “All orders over $500.” JSONB by itself doesn’t let you do this as easily as you’d like. That’s where jsonb_to_recordset() comes in.

Working with jsonb_to_recordset()

The jsonb_to_recordset() function lets you turn an array of JSONB objects into table rows. It literally turns each array element into a row, and the keys become columns. This function is a lifesaver when your data is deeply nested or has arrays of objects.

Syntax

SELECT *
FROM jsonb_to_recordset('[ JSONB array ]') AS alias(column1 TYPE, column2 TYPE, ...);

• [ JSONB array ]: the array of JSON objects you want to pull data from.
• AS alias: gives a temporary name to the resulting table.
• column1 TYPE, column2 TYPE: defines what the columns should be called and what data types they use (like INTEGER, TEXT, NUMERIC).

Example: turning a JSONB array into rows

Let’s say we have this table:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_name TEXT,
    products JSONB
);

And the table has this data:

Now the task: show a list of all products for all orders in a table. Here’s how you do it with jsonb_to_recordset():

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC);

Result:

Example: filtering data

Let’s make it a bit harder. We want to show only those products from orders that cost more than $100:

SELECT
    o.id AS order_id,
    o.customer_name,
    p.id AS product_id,
    p.product_name,
    p.price
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
WHERE
    p.price > 100;

Result:

Example: aggregating data

How about calculating the total sum of all products in orders? Just use aggregate functions:

SELECT
    o.customer_name,
    SUM(p.price) AS total_amount
FROM
    orders AS o,
    jsonb_to_recordset(o.products) AS p(id INTEGER, product_name TEXT, price NUMERIC)
GROUP BY
    o.customer_name;

Result:

Important notes

Make sure the JSON array structure is the same for all objects. If an object has different keys or nested structures, you might get errors or weird behavior.

Set the right data types for the extracted columns. For example, if a key holds a date, use DATE; for numbers — NUMERIC or INTEGER.

Remember, jsonb_to_recordset() only works with JSONB arrays; it won’t work with single objects.

Common mistakes and how to avoid them

Wrong data types: if your JSONB array has values with different types (like a string instead of a number), you’ll get an error. It’s a good idea to cast your data to the right format before using the function.

Using the wrong keys: if a key is missing in one of the array objects, you’ll get an error. Check your data structure before running the query.

No data: if the JSONB column is empty (NULL), the function won’t return any results. In these cases, add checks, like COALESCE().

Real-world usage

jsonb_to_recordset() is used all over the place in real projects, including order processing, report analysis, user action logging, and handling external APIs. For example:

• In online stores, it’s easy to turn product arrays into tables and build reports.
• A REST API might return data as JSON, which is super convenient to analyze with PostgreSQL.
• Analytics apps often use this function to process complex multi-level data.