jsonb_each es una funcion que devuelve un conjunto de filas (set-returning function): toma un solo objeto JSON y lo expande en una fila por cada par (key, value). Es la operacion inversa de JSONB_OBJECT_AGG: alli las filas se plegaban en un mapa, aqui el mapa vuelve a ser filas que puedes filtrar, unir y agregar con SQL normal.
Sintaxis y ejemplo basico
La funcion recibe un argumento de tipo jsonb y devuelve dos columnas: key de tipo text y value de tipo jsonb. El lugar natural para llamarla es la clausula FROM, no la lista del SELECT, de modo que cada entrada del objeto se convierte en su propia fila.
SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);
El resultado son dos filas: ('theme', "dark") y ('lang', "en"). Algunos comportamientos que conviene recordar:
value sigue siendo jsonb, asi que los valores de texto vuelven entre comillas: "dark", no dark.- El orden de las filas no esta garantizado: un objeto es un mapa, no una lista.
- Si el argumento es un array o un escalar en lugar de un objeto, se produce un error. Para expandir un array usa
jsonb_array_elements.
- Con una entrada
NULL la funcion devuelve cero filas, no una fila con NULL.
Expandir una columna JSON en filas
Lo habitual es que el objeto este en una columna, no en un literal. Supongamos que users tiene un campo prefs jsonb con un conjunto arbitrario de ajustes. Para expandirlo en filas, coloca la funcion en el FROM tras una coma (es un 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 se convierte en su propia fila ligada al usuario. La verdadera fuerza es que las claves pueden ser dinamicas: no necesitas conocer sus nombres de antemano, como tendrias que hacer con prefs->>'theme'. Es ideal para recorrer una forma desconocida o contar que claves aparecen siquiera.
Trampa: si prefs llega a ser NULL, el join implicito descarta en silencio a ese usuario del resultado. Para conservar la fila del usuario, usa un LEFT JOIN LATERAL ... ON true explicito y pasale COALESCE(prefs, '{}'::jsonb).
Filtrar y agregar entradas
Una vez que las entradas del objeto son filas, se aplica todo el arsenal habitual de SQL. Cuenta cuantas claves tiene cada objeto de ajustes:
SELECT u.id, count(*) AS pref_count
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY u.id;
O averigua que claves son las mas comunes en toda la tabla, una forma clasica de explorar un JSON "salvaje" que se escribio sin 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;
Puedes filtrar por clave o por valor. Encuentra usuarios con al menos un ajuste igual a la cadena "on":
SELECT DISTINCT u.id, u.email
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE e.value = '"on"'::jsonb;
Fijate en la comparacion: como value es jsonb, el literal tambien debe ser JSON valido, de ahi las comillas dentro de '"on"'.
La variante _text: cuando no quieres valores JSON
Si los valores son en realidad solo texto, las comillas a su alrededor solo estorban. Para eso esta jsonb_each_text: devuelve el mismo par, pero value es text, sin comillas envolventes.
SELECT key, value
FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);
La diferencia importa cuando necesitas comparar valores o convertirlos a un 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';
Con jsonb_each lo mismo exigiria un segundo cast para quitar el tipo. Cuidado con la trampa: jsonb_each_text tambien convierte a cadena los objetos y arrays anidados, que vuelven como un texto JSON serializado, y un null de JSON se vuelve NULL de SQL.
Notas sobre otras bases de datos
Expandir un objeto JSON en filas es una fortaleza clara de PostgreSQL. MySQL no tiene un equivalente directo de jsonb_each: normalmente extraes las claves con JSON_KEYS y luego expandes ese array con JSON_TABLE, lo cual es mas verboso. En ClickHouse, los objetos JSON suelen guardarse como tipo Map, y recorres con mapKeys y mapValues mas arrayJoin en vez de una funcion dedicada. Cuando necesitas convertir un mapa de vuelta en filas y trabajarlo con SQL normal, jsonb_each y jsonb_each_text en PostgreSQL siguen siendo la herramienta mas directa.
jsonb_eaches una funcion que devuelve un conjunto de filas (set-returning function): toma un solo objeto JSON y lo expande en una fila por cada par(key, value). Es la operacion inversa deJSONB_OBJECT_AGG: alli las filas se plegaban en un mapa, aqui el mapa vuelve a ser filas que puedes filtrar, unir y agregar con SQL normal.Sintaxis y ejemplo basico
La funcion recibe un argumento de tipo
jsonby devuelve dos columnas:keyde tipotextyvaluede tipojsonb. El lugar natural para llamarla es la clausulaFROM, no la lista delSELECT, de modo que cada entrada del objeto se convierte en su propia fila.SELECT key, value FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);El resultado son dos filas:
('theme', "dark")y('lang', "en"). Algunos comportamientos que conviene recordar:valuesigue siendojsonb, asi que los valores de texto vuelven entre comillas:"dark", nodark.jsonb_array_elements.NULLla funcion devuelve cero filas, no una fila conNULL.Expandir una columna JSON en filas
Lo habitual es que el objeto este en una columna, no en un literal. Supongamos que
userstiene un campoprefs jsonbcon un conjunto arbitrario de ajustes. Para expandirlo en filas, coloca la funcion en elFROMtras una coma (es un 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
prefsse convierte en su propia fila ligada al usuario. La verdadera fuerza es que las claves pueden ser dinamicas: no necesitas conocer sus nombres de antemano, como tendrias que hacer conprefs->>'theme'. Es ideal para recorrer una forma desconocida o contar que claves aparecen siquiera.Filtrar y agregar entradas
Una vez que las entradas del objeto son filas, se aplica todo el arsenal habitual de SQL. Cuenta cuantas claves tiene cada objeto de ajustes:
SELECT u.id, count(*) AS pref_count FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY u.id;O averigua que claves son las mas comunes en toda la tabla, una forma clasica de explorar un JSON "salvaje" que se escribio sin 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;Puedes filtrar por clave o por valor. Encuentra usuarios con al menos un ajuste igual a la cadena
"on":SELECT DISTINCT u.id, u.email FROM users AS u, jsonb_each(u.prefs) AS e WHERE e.value = '"on"'::jsonb;Fijate en la comparacion: como
valueesjsonb, el literal tambien debe ser JSON valido, de ahi las comillas dentro de'"on"'.La variante _text: cuando no quieres valores JSON
Si los valores son en realidad solo texto, las comillas a su alrededor solo estorban. Para eso esta
jsonb_each_text: devuelve el mismo par, perovalueestext, sin comillas envolventes.-- value comes back as plain text: dark, not "dark" SELECT key, value FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);La diferencia importa cuando necesitas comparar valores o convertirlos a un 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';Con
jsonb_eachlo mismo exigiria un segundo cast para quitar el tipo. Cuidado con la trampa:jsonb_each_texttambien convierte a cadena los objetos y arrays anidados, que vuelven como un texto JSON serializado, y unnullde JSON se vuelveNULLde SQL.Notas sobre otras bases de datos
Expandir un objeto JSON en filas es una fortaleza clara de PostgreSQL. MySQL no tiene un equivalente directo de
jsonb_each: normalmente extraes las claves conJSON_KEYSy luego expandes ese array conJSON_TABLE, lo cual es mas verboso. En ClickHouse, los objetos JSON suelen guardarse como tipoMap, y recorres conmapKeysymapValuesmasarrayJoinen vez de una funcion dedicada. Cuando necesitas convertir un mapa de vuelta en filas y trabajarlo con SQL normal,jsonb_eachyjsonb_each_texten PostgreSQL siguen siendo la herramienta mas directa.