jsonb_array_elements: expandir un array JSON en filas en PostgreSQL

Cuando un array vive dentro de una columna JSONB, no puedes filtrar ni unir por sus elementos directamente: SQL no sabe "entrar" en un unico valor. jsonb_array_elements lo resuelve expandiendo el array en un conjunto de filas, una fila por elemento. Es el analogo JSON de UNNEST, y es el punto de partida de cualquier analisis sobre arrays anidados.

Una fila por elemento

jsonb_array_elements es una funcion que devuelve un conjunto (set-returning). Ponla en el FROM junto a una tabla con una coma, y cada elemento del array se convierte en su propia fila. Esa coma es un LATERAL implicito, asi que la funcion ve las columnas de la tabla a su izquierda.

-- 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;

Si prefs -> 'tags' tiene tres elementos, cada usuario produce tres filas. La columna por defecto se llama value y contiene jsonb. Ten presentes sus propiedades clave:

• Devuelve jsonb, asi que e.value es "vip" con comillas, no vip.
• Conserva el orden de los elementos del array.
• Un array vacio [] no produce ninguna fila, asi que ese usuario desaparece del resultado.

Filtrar y unir por elementos

Una vez expandido el array, filtras y agrupas por sus elementos como si fueran filas normales. Este es el movimiento clave: "encontrar a todos cuyo array contiene 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"';

Fijate en la comparacion contra '"vip"': es un literal de cadena jsonb, con sus comillas internas. Para escribir un 'vip' normal, usa la variante de texto (mas abajo). Asi se expanden las lineas de un pedido y se suma el ingreso 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: obtener el indice

A veces importa la posicion: la primera etiqueta, el paso de un embudo, el numero de orden. WITH ORDINALITY agrega una columna con el numero de fila, empezando en 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) nombra las salidas: tag es el valor, pos es el indice. La numeracion empieza en 1, a diferencia de los operadores flecha, donde el indice del array empieza en cero.

La variante _text: sin comillas

jsonb_array_elements_text hace la misma expansion pero devuelve text en lugar de jsonb. Eso elimina el dolor de las comillas: compara y muestra los elementos como cadenas normales.

-- 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';

La regla es sencilla: un array de cadenas o numeros, usa _text; un array de objetos en los que necesitas bajar con ->/->>, usa la variante jsonb normal.

Trampas habituales

• Un array vacio o un NULL de SQL no produce ninguna fila, sin error. Una funcion en el FROM se comporta como un CROSS JOIN: cero elementos significa cero filas, y un usuario sin etiquetas simplemente desaparece del resultado. Aqui no hay error: pasar un NULL de SQL a la funcion devuelve un conjunto vacio, igual que []. Para conservar esas filas, usa LEFT JOIN LATERAL ... ON true.
• Un valor JSON que no es un array si es un error. El conjunto vacio de arriba solo aplica a un NULL de SQL o a []. Si el valor JSON es un objeto o un escalar, la funcion lanza cannot extract elements from a scalar/object. La trampa es el escalar JSON null: jsonb_array_elements('null'::jsonb) falla, porque ese null es un valor JSON que no es un array, no un NULL de SQL. Protegete: jsonb_array_elements(COALESCE(col -> 'tags', '[]'::jsonb)).
• value frente a '"value"'. En la variante normal el elemento es jsonb, asi que comparas una cadena contra '"vip"'. Equivocarse aqui es una causa habitual de un resultado vacio sin explicacion.

-- 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;

MySQL no tiene un equivalente directo antes de la 8.0: usa JSON_TABLE(doc, '$.tags[*]' COLUMNS(tag VARCHAR(50) PATH '$')) para expandir un array en filas. ClickHouse se apoya en arrayJoin(JSONExtractArrayRaw(col)), o guarda los datos en una columna Array y llama a arrayJoin directamente. La idea es universal -- array a filas -- pero jsonb_array_elements es el camino mas directo en PostgreSQL.