jsonb_array_elements: expandir um array JSON em linhas no PostgreSQL

Quando um array vive dentro de uma coluna JSONB, voce nao pode filtrar nem juntar por seus itens diretamente: o SQL nao sabe "entrar" em um unico valor. jsonb_array_elements resolve isso expandindo o array em um conjunto de linhas, uma linha por elemento. E o analogo JSON do UNNEST, e e por onde comeca qualquer analise sobre arrays aninhados.

Uma linha por elemento

jsonb_array_elements e uma funcao que retorna um conjunto (set-returning). Coloque-a no FROM ao lado de uma tabela com uma virgula, e cada elemento do array vira sua propria linha. Essa virgula e um LATERAL implicito, entao a funcao enxerga as colunas da tabela a sua esquerda.

-- users.prefs holds a jsonb array of tags: ["vip", "eu", "beta"]
SELECT u.id, e.value AS tag
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e;

Se prefs -> 'tags' tem tres elementos, cada usuario produz tres linhas. A coluna padrao se chama value e contem jsonb. Tenha em mente as propriedades principais:

• Retorna jsonb, entao e.value e "vip" com aspas, nao vip.
• Preserva a ordem dos elementos do array.
• Um array vazio [] nao produz nenhuma linha, entao esse usuario some do resultado.

Filtrar e juntar por itens

Depois de expandir o array, voce filtra e agrupa por seus itens como se fossem linhas comuns. Este e o movimento central: "encontrar todos cujo array contem X".

-- find users whose tags array contains 'vip'
SELECT DISTINCT u.id, u.email
FROM users u, jsonb_array_elements(u.prefs -> 'tags') AS e
WHERE e.value = '"vip"';

Repare na comparacao contra '"vip"': e um literal de string jsonb, com aspas internas. Para escrever um 'vip' comum, use a variante de texto (abaixo). Veja como expandir os itens de um pedido e somar a receita por SKU:

-- orders.payload = {"items": [{"sku": "A1", "qty": 2, "price": 10}, ...]}
SELECT
  item ->> 'sku'                                   AS sku,
  sum((item ->> 'qty')::int * (item ->> 'price')::numeric) AS revenue
FROM orders o, jsonb_array_elements(o.payload -> 'items') AS item
WHERE o.status = 'paid'
GROUP BY item ->> 'sku'
ORDER BY revenue DESC;

WITH ORDINALITY: obter o indice

As vezes a posicao importa: a primeira tag, o passo de um funil, o numero de ordem. WITH ORDINALITY adiciona uma coluna com o numero da linha, comecando em 1.

-- keep only the first two tags per user, with their position
SELECT u.id, t.tag, t.pos
FROM users u,
     jsonb_array_elements_text(u.prefs -> 'tags') WITH ORDINALITY AS t(tag, pos)
WHERE t.pos <= 2;

Aqui t(tag, pos) nomeia as saidas: tag e o valor, pos e o indice. A numeracao comeca em 1, ao contrario dos operadores seta, onde o indice do array comeca em zero.

A variante _text: sem aspas

jsonb_array_elements_text faz a mesma expansao, mas retorna text em vez de jsonb. Isso elimina a dor das aspas: compare e exiba os itens como strings comuns.

-- compare with plain 'vip', no JSON quoting needed
SELECT DISTINCT u.id
FROM users u, jsonb_array_elements_text(u.prefs -> 'tags') AS tag
WHERE tag = 'vip';

A regra e simples: um array de strings ou numeros, use _text; um array de objetos em que voce precisa descer com ->/->>, use a variante jsonb comum.

Pegadinhas

• Um array vazio ou um NULL de SQL nao produz nenhuma linha, sem erro. Uma funcao no FROM se comporta como um CROSS JOIN: zero elementos significa zero linhas, e um usuario sem tags simplesmente some do resultado. Aqui nao ha erro: passar um NULL de SQL para a funcao retorna um conjunto vazio, igual a []. Para manter essas linhas, use LEFT JOIN LATERAL ... ON true.
• Um valor JSON que nao e um array gera erro. O conjunto vazio acima vale apenas para um NULL de SQL ou para []. Se o valor JSON for um objeto ou um escalar, a funcao lanca cannot extract elements from a scalar/object. A pegadinha e o escalar JSON null: jsonb_array_elements('null'::jsonb) falha, porque esse null e um valor JSON que nao e um array, nao um NULL de SQL. Proteja-se: jsonb_array_elements(COALESCE(col -> 'tags', '[]'::jsonb)).
• value contra '"value"'. Na variante comum o elemento e jsonb, entao voce compara uma string contra '"vip"'. Errar aqui e uma causa frequente de um resultado vazio sem explicacao.

-- keep every user, even those with an empty or missing tags array
SELECT u.id, e.value AS tag
FROM users u
LEFT JOIN LATERAL jsonb_array_elements(COALESCE(u.prefs -> 'tags', '[]'::jsonb)) AS e ON true;

O MySQL nao tem um equivalente direto antes da 8.0: use JSON_TABLE(doc, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$')) para expandir um array em linhas. O ClickHouse se apoia em arrayJoin(JSONExtractArrayRaw(col)), ou guarda os dados em uma coluna Array e chama arrayJoin diretamente. A ideia e universal -- array em linhas -- mas jsonb_array_elements e o caminho mais direto no PostgreSQL.