jsonb_each e uma funcao que retorna um conjunto de linhas (set-returning function): ela pega um unico objeto JSON e o expande em uma linha por par (key, value). E a operacao inversa de JSONB_OBJECT_AGG: la as linhas eram dobradas em um mapa, aqui o mapa volta a ser linhas que voce pode filtrar, juntar e agregar com SQL comum.
Sintaxe e exemplo basico
A funcao recebe um argumento do tipo jsonb e retorna duas colunas: key do tipo text e value do tipo jsonb. O lugar natural para chama-la e a clausula FROM, nao a lista do SELECT, de modo que cada entrada do objeto vira a sua propria linha de resultado.
SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);
O resultado sao duas linhas: ('theme', "dark") e ('lang', "en"). Alguns comportamentos que vale lembrar:
value continua sendo jsonb, entao valores de texto voltam entre aspas: "dark", nao dark.- A ordem das linhas nao e garantida: um objeto e um mapa, nao uma lista.
- Se o argumento for um array ou escalar em vez de um objeto, ocorre um erro. Para expandir um array use
jsonb_array_elements.
- Com uma entrada
NULL a funcao retorna zero linhas, nao uma linha com NULL.
Expandir uma coluna JSON em linhas
Na maioria das vezes o objeto esta em uma coluna, nao em um literal. Suponha que users tenha um campo prefs jsonb com um conjunto arbitrario de configuracoes. Para expandi-lo em linhas, coloque a funcao no FROM apos uma virgula (e um join LATERAL implicito):
SELECT u.id, u.email, e.key, e.value
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE u.id = 1;
Cada par de prefs vira a sua propria linha ligada ao usuario. A verdadeira forca e que as chaves podem ser dinamicas: voce nao precisa saber os nomes de antemao, como precisaria com prefs->>'theme'. Isso e ideal para percorrer uma forma desconhecida ou contar quais chaves chegam a aparecer.
Pegadinha: se prefs chegar a ser NULL, o join implicito descarta esse usuario do resultado em silencio. Para manter a linha do usuario, use um LEFT JOIN LATERAL ... ON true explicito e passe COALESCE(prefs, '{}'::jsonb).
Filtrar e agregar entradas
Uma vez que as entradas do objeto viraram linhas, todo o arsenal comum do SQL se aplica. Conte quantas chaves cada objeto de configuracoes tem:
SELECT u.id, count(*) AS pref_count
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY u.id;
Ou descubra quais chaves sao mais comuns em toda a tabela, uma forma classica de inspecionar um JSON "selvagem" que foi escrito sem esquema:
SELECT e.key, count(*) AS used_by
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY e.key
ORDER BY used_by DESC;
Voce pode filtrar por chave ou por valor. Encontre usuarios com pelo menos uma configuracao igual a string "on":
SELECT DISTINCT u.id, u.email
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE e.value = '"on"'::jsonb;
Repare na comparacao: como value e jsonb, o literal tambem precisa ser JSON valido, dai as aspas dentro de '"on"'.
A variante _text: quando voce nao quer valores JSON
Se os valores sao na verdade apenas texto, as aspas em volta deles so atrapalham. E para isso que existe jsonb_each_text: ela retorna o mesmo par, mas value e text, sem as aspas externas.
SELECT key, value
FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);
A diferenca importa quando voce precisa comparar valores ou converte-los para um numero:
SELECT u.id, (e.value)::int AS limit_value
FROM users AS u, jsonb_each_text(u.prefs) AS e
WHERE e.key = 'rate_limit';
Com jsonb_each a mesma coisa exigiria um segundo cast para remover o tipo. Atencao a pegadinha: jsonb_each_text tambem transforma em string os objetos e arrays aninhados, que voltam como um texto JSON serializado, e um null de JSON vira um NULL de SQL.
Notas sobre outros bancos
Expandir um objeto JSON em linhas e claramente um ponto forte do PostgreSQL. O MySQL nao tem um equivalente direto de jsonb_each: normalmente voce extrai as chaves com JSON_KEYS e depois expande esse array com JSON_TABLE, o que e mais verboso. No ClickHouse, objetos JSON costumam ser guardados como tipo Map, e voce percorre com mapKeys e mapValues mais arrayJoin em vez de uma funcao dedicada. Quando voce precisa transformar um mapa de volta em linhas e trabalha-lo com SQL comum, jsonb_each e jsonb_each_text no PostgreSQL continuam sendo a ferramenta mais direta.
jsonb_eache uma funcao que retorna um conjunto de linhas (set-returning function): ela pega um unico objeto JSON e o expande em uma linha por par(key, value). E a operacao inversa deJSONB_OBJECT_AGG: la as linhas eram dobradas em um mapa, aqui o mapa volta a ser linhas que voce pode filtrar, juntar e agregar com SQL comum.Sintaxe e exemplo basico
A funcao recebe um argumento do tipo
jsonbe retorna duas colunas:keydo tipotextevaluedo tipojsonb. O lugar natural para chama-la e a clausulaFROM, nao a lista doSELECT, de modo que cada entrada do objeto vira a sua propria linha de resultado.SELECT key, value FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);O resultado sao duas linhas:
('theme', "dark")e('lang', "en"). Alguns comportamentos que vale lembrar:valuecontinua sendojsonb, entao valores de texto voltam entre aspas:"dark", naodark.jsonb_array_elements.NULLa funcao retorna zero linhas, nao uma linha comNULL.Expandir uma coluna JSON em linhas
Na maioria das vezes o objeto esta em uma coluna, nao em um literal. Suponha que
userstenha um campoprefs jsonbcom um conjunto arbitrario de configuracoes. Para expandi-lo em linhas, coloque a funcao noFROMapos uma virgula (e um joinLATERALimplicito):SELECT u.id, u.email, e.key, e.value FROM users AS u, jsonb_each(u.prefs) AS e WHERE u.id = 1;Cada par de
prefsvira a sua propria linha ligada ao usuario. A verdadeira forca e que as chaves podem ser dinamicas: voce nao precisa saber os nomes de antemao, como precisaria comprefs->>'theme'. Isso e ideal para percorrer uma forma desconhecida ou contar quais chaves chegam a aparecer.Filtrar e agregar entradas
Uma vez que as entradas do objeto viraram linhas, todo o arsenal comum do SQL se aplica. Conte quantas chaves cada objeto de configuracoes tem:
SELECT u.id, count(*) AS pref_count FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY u.id;Ou descubra quais chaves sao mais comuns em toda a tabela, uma forma classica de inspecionar um JSON "selvagem" que foi escrito sem esquema:
SELECT e.key, count(*) AS used_by FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY e.key ORDER BY used_by DESC;Voce pode filtrar por chave ou por valor. Encontre usuarios com pelo menos uma configuracao igual a string
"on":SELECT DISTINCT u.id, u.email FROM users AS u, jsonb_each(u.prefs) AS e WHERE e.value = '"on"'::jsonb;Repare na comparacao: como
valueejsonb, o literal tambem precisa ser JSON valido, dai as aspas dentro de'"on"'.A variante _text: quando voce nao quer valores JSON
Se os valores sao na verdade apenas texto, as aspas em volta deles so atrapalham. E para isso que existe
jsonb_each_text: ela retorna o mesmo par, masvalueetext, sem as aspas externas.-- value comes back as plain text: dark, not "dark" SELECT key, value FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);A diferenca importa quando voce precisa comparar valores ou converte-los para um numero:
-- read a numeric flag straight from a dynamic key SELECT u.id, (e.value)::int AS limit_value FROM users AS u, jsonb_each_text(u.prefs) AS e WHERE e.key = 'rate_limit';Com
jsonb_eacha mesma coisa exigiria um segundo cast para remover o tipo. Atencao a pegadinha:jsonb_each_texttambem transforma em string os objetos e arrays aninhados, que voltam como um texto JSON serializado, e umnullde JSON vira umNULLde SQL.Notas sobre outros bancos
Expandir um objeto JSON em linhas e claramente um ponto forte do PostgreSQL. O MySQL nao tem um equivalente direto de
jsonb_each: normalmente voce extrai as chaves comJSON_KEYSe depois expande esse array comJSON_TABLE, o que e mais verboso. No ClickHouse, objetos JSON costumam ser guardados como tipoMap, e voce percorre commapKeysemapValuesmaisarrayJoinem vez de uma funcao dedicada. Quando voce precisa transformar um mapa de volta em linhas e trabalha-lo com SQL comum,jsonb_eachejsonb_each_textno PostgreSQL continuam sendo a ferramenta mais direta.