Extraindo dados aninhados: jsonb_to_recordset()

Agora bora pra cenários mais cabulosos de trabalhar com dados em formato JSONB — extrair dados aninhados e transformar eles em linhas de tabela. Tu pode perguntar: pra quê isso? Simples! Imagina que te deram um objeto JSON com um array de compras e pediram pra calcular o total de todas as compras ou mostrar elas em formato de tabela pra um relatório. Bora ver como faz isso agora!

Por que não dá pra trabalhar com JSON só como texto ou estrutura? Olha só esse cenário. Em muitos apps reais, os dados ficam guardados como arrays de JSON:

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

Isso é prático, mas na hora de analisar os dados, quase sempre tu precisa transformar o array em tabela pra fazer coisas tipo filtrar, ordenar e agregar. Imagina: “Todos os pedidos acima de 500 dólares”. O JSONB sozinho não deixa fazer isso de um jeito tão fácil quanto a gente quer. É aí que entra o jsonb_to_recordset().

Trabalhando com jsonb_to_recordset()

A função jsonb_to_recordset() transforma um array de objetos JSONB em linhas de tabela. Ela literalmente pega cada item do array e vira uma linha, e as chaves viram colunas. Essa função é indispensável quando os dados são bem aninhados ou têm arrays de objetos.

Sintaxe

SELECT *
FROM jsonb_to_recordset('[ array JSONB ]') AS alias(coluna1 TIPO, coluna2 TIPO, ...);

• [ array JSONB ]: o array de objetos JSON de onde a gente vai tirar os dados.
• AS alias: cria um nome temporário pra tabela que vai sair.
• coluna1 TIPO, coluna2 TIPO: define como vão se chamar as colunas e quais tipos de dados elas vão usar (tipo INTEGER, TEXT, NUMERIC).

Exemplo: transformando um array JSONB em linhas

Imagina que a gente tem essa tabela:

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

E na tabela tem esses dados:

Agora o desafio: mostrar a lista de todos os produtos de todos os pedidos em formato de tabela. Olha como faz usando 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);

Resultado:

Exemplo: filtrando dados

Bora deixar mais interessante. Queremos mostrar só os produtos dos pedidos que custam mais de 100 dólares:

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;

Resultado:

Exemplo: agregando dados

Que tal calcular o total de todos os produtos nos pedidos? Só usar funções de agregação:

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;

Resultado:

Dicas importantes

Garante que a estrutura do array JSON seja igual pra todos os objetos. Se algum objeto tiver chaves diferentes ou estruturas aninhadas, pode dar erro ou um resultado estranho.

Define certinho os tipos de dados das colunas que tu vai extrair. Por exemplo, se a chave tem uma data, usa DATE, pra números — NUMERIC ou INTEGER.

Lembra que jsonb_to_recordset() só transforma arrays JSONB; com objetos únicos ela não funciona.

Erros comuns e como evitar

Usar tipos de dados errados: se no array JSONB tiver valores com tipos diferentes (tipo string no lugar de número), vai dar erro. Recomendo converter os dados pro formato certo antes de usar a função.

Acessar chaves erradas: se uma chave não existir em algum objeto do array, vai dar erro. Confere a estrutura dos dados antes de rodar a query.

Falta de dados: se a coluna JSONB estiver vazia (NULL), a função não retorna nada. Nesses casos, usa checagem tipo COALESCE().

Aplicação prática

jsonb_to_recordset() é muito usado em tarefas reais, tipo processar pedidos, analisar relatórios, logar ações de usuário e tratar dados de APIs externas. Por exemplo:

• Em lojas online, dá pra transformar arrays de produtos em tabelas e montar relatórios fácil.
• REST API pode devolver dados em JSON, que tu analisa de boa com PostgreSQL.
• Apps de analytics usam muito essa função pra processar dados complexos e cheios de níveis.