jsonb_build_array en PostgreSQL: arrays JSON con valores de tipos mixtos

jsonb_build_array es una funcion de PostgreSQL que arma un array JSON a partir de los argumentos que listes, y cada argumento puede ser de un tipo distinto. A diferencia de un array SQL normal, donde todos los elementos comparten un mismo tipo, aqui conviven numeros, cadenas, fechas, booleanos y objetos anidados.

Sintaxis basica

Le pasas cualquier numero de argumentos y devuelve un valor jsonb que conserva el orden de los elementos.

SELECT jsonb_build_array(1, 'two', true, NULL);
-- [1, "two", true, null]

La diferencia clave frente a un array de PostgreSQL es la heterogeneidad. La llamada de abajo es imposible con ARRAY[...] porque los elementos tienen tipos distintos:

SELECT jsonb_build_array(id, name, salary, dept)
FROM employees
WHERE id = 42;
-- [42, "Ada", 95000.00, "engineering"]

Cada argumento pasa por la conversion habitual a JSON: los numeros siguen siendo numeros, el texto se vuelve cadena, boolean se convierte en true/false y NULL en JSON null. Una fecha o un timestamp se transforma en una cadena con formato ISO.

Tuplas y filas de tabla como JSON

jsonb_build_array brilla cuando quieres una "tupla" de forma fija: un array posicional en lugar de un objeto con claves. Es mas compacto que un objeto y encaja con datos tabulares donde el cliente ya conoce el orden de las columnas.

SELECT jsonb_agg(
         jsonb_build_array(id, email, country, created_at)
       ) AS rows
FROM users
WHERE country = 'ES';
-- [[1,"a@x.io","ES","2024-01-10T00:00:00"], [2,"b@x.io","ES", ...]]

Aqui jsonb_build_array forma una tupla por fila y jsonb_agg pliega todas las filas en un array de arrays: un volcado compacto del resultado.

Anidamiento con jsonb_build_object

La verdadera potencia aparece al combinarlo con jsonb_build_object. Un array puede contener objetos, y los objetos pueden contener arrays; el anidamiento no tiene un limite fijo.

SELECT jsonb_build_object(
         'user_id', u.id,
         'recent_orders',
         (SELECT jsonb_agg(
                   jsonb_build_array(o.id, o.amount, o.status)
                 )
          FROM orders o
          WHERE o.user_id = u.id)
       ) AS payload
FROM users u
WHERE u.id = 7;
-- {"user_id": 7, "recent_orders": [[100, 49.90, "paid"], [101, 12.00, "pending"]]}

El array interno es una tupla posicional de pedido, y el objeto externo le da claves legibles. Asi se construyen respuestas de API sin un paso de ensamblado intermedio en la aplicacion.

La diferencia con to_jsonb de un array

Una confusion habitual es jsonb_build_array(a, b, c) frente a to_jsonb(ARRAY[a, b, c]). Los resultados parecen similares a primera vista, pero la semantica es distinta.

• jsonb_build_array acepta argumentos de tipos mixtos y conserva sus tipos JSON nativos.
• to_jsonb de un array SQL exige que el array sea de un solo tipo y lo convierte como un todo.

-- Funciona: tipos mixtos
SELECT jsonb_build_array(1, 'x', true);   -- [1, "x", true]

-- Error: ARRAY necesita un tipo comun
SELECT to_jsonb(ARRAY[1, 'x', true]);     -- ERROR: cannot mix types

-- Funciona: array de un solo tipo
SELECT to_jsonb(ARRAY[1, 2, 3]);          -- [1, 2, 3]

Trampa: PostgreSQL intentara forzar los literales de ARRAY[1, 'x'] a un tipo comun y fallara con un error de incompatibilidad de tipos. Cuando realmente necesitas un conjunto de valores de tipos distintos, usa jsonb_build_array, y reserva to_jsonb para un array ya homogeneo o para una fila de tabla (to_jsonb(t.*)).

Otro detalle con NULL: en jsonb_build_array, un NULL de SQL se vuelve JSON null y permanece en el array en lugar de descartarse. Si necesitas quitar los valores vacios, filtralos antes en una subconsulta.

Otras bases de datos

• MySQL usa JSON_ARRAY(...), un equivalente directo con la misma idea de argumentos mixtos. Para objetos existe JSON_OBJECT(...).
• ClickHouse trata el JSON de forma distinta historicamente: hay un tipo JSON y funciones como toJSONString, pero no un constructor posicional "al estilo Postgres"; las tuplas se arman con tuple() y luego se serializan.

Si tu codigo viaja entre motores, manten la capa de formato JSON en un solo lugar: la sintaxis cambia lo suficiente como para que merezca la pena.