JSONB_BUILD_OBJECT en PostgreSQL: construir un objeto JSON a partir de columnas con tipos

jsonb_build_object arma un objeto JSON dentro de la propia consulta a partir de argumentos alternos clave, valor, clave, valor, .... A diferencia de concatenar cadenas a mano, conserva los tipos de los valores: un numero sigue siendo numero, un booleano sigue siendo booleano y NULL se vuelve un null JSON real. Es la herramienta de cabecera para devolver un payload de API listo o una estructura anidada en un solo SELECT.

Sintaxis y ejemplo basico

Los argumentos van en pares: las posiciones impares son claves (convertidas a texto) y las pares son valores de cualquier tipo. El resultado es jsonb; su hermano json_build_object devuelve json (texto sin normalizar).

SELECT jsonb_build_object(
    'id', id,
    'email', email,
    'country', country
) AS payload
FROM users;

Algunos comportamientos que conviene conocer:

• El numero de argumentos debe ser par, o salta el error argument list must have even number of elements.
• Los tipos de los valores se conservan: amount numeric se vuelve un numero JSON, no una cadena entre comillas.
• Un valor NULL produce un null JSON, y la clave permanece en el objeto.
• Un NULL en una posicion de clave se rechaza con error; las claves siempre deben estar presentes.

Un payload de API por fila

El caso estrella es dar forma a un payload de API dentro de la base de datos sin duplicar nombres de campos en el codigo de la aplicacion. Construir una "ficha" de usuario con un par de campos derivados es natural:

SELECT jsonb_build_object(
    'user_id', u.id,
    'name', u.name,
    'is_verified', (u.email IS NOT NULL),
    'signup_year', EXTRACT(YEAR FROM u.created_at)
) AS user_card
FROM users u;

Aqui is_verified es un booleano JSON real, no la cadena "true". Esa es la ventaja clave frente a la concatenacion: los tipos sobreviven y PostgreSQL se encarga del entrecomillado y el escapado por ti.

Trampa: las claves no se deduplican al construir en json_build_object (el tipo json), pero jsonb conserva el ultimo valor de una clave repetida y descarta el resto. Si indicas una clave dos veces, el resultado jsonb guarda solo el par final, asi que el orden de los argumentos decide cual gana.

Anidamiento: objetos dentro de objetos y arrays

jsonb_build_object se anida libremente dentro de si mismo y de jsonb_build_array, de modo que puedes construir estructuras de cualquier profundidad, como un pedido con un bloque de cliente anidado y una lista de etiquetas:

SELECT jsonb_build_object(
    'order_id', o.id,
    'amount', o.amount,
    'status', o.status,
    'customer', jsonb_build_object(
        'id', u.id,
        'email', u.email
    ),
    'flags', jsonb_build_array('priority', o.status)
) AS order_json
FROM orders o
JOIN users u ON u.id = o.user_id;

Para construir un array de objetos hijos (todos los pedidos de un usuario), combina jsonb_build_object con el agregado jsonb_agg:

SELECT jsonb_build_object(
    'user_id', u.id,
    'orders', jsonb_agg(
        jsonb_build_object('id', o.id, 'amount', o.amount)
        ORDER BY o.created_at
    )
) AS user_with_orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id;

Cuando usar to_jsonb en su lugar

Cuando quieres JSON con todas las columnas de una fila y sin renombrar, to_jsonb(row) es mas simple: mapea la fila por nombre de columna automaticamente.

SELECT to_jsonb(e) AS employee_json
FROM employees e;

Elegir entre ambas es directo:

• to_jsonb(t) es un volcado rapido de la fila entera; las claves son los nombres de columna y no puedes elegir ni renombrar campos.
• jsonb_build_object(...) da control total sobre el conjunto de claves, los nombres y el orden, ideal para un contrato de API estable.
• Hibrido: to_jsonb(e) - 'salary' quita un campo, y to_jsonb(e) || jsonb_build_object('bonus', e.salary * 0.1) agrega uno calculado.

MySQL y ClickHouse

MySQL ofrece JSON_OBJECT('id', id, 'name', name) con la misma logica de pares alternos, y los tipos de los valores tambien se conservan. No hay un equivalente directo de to_jsonb(row), asi que enumeras las claves a mano. Para arrays de objetos, usa JSON_ARRAYAGG(JSON_OBJECT(...)).

ClickHouse trabajo historicamente con JSON en cadena mediante funciones como toJSONString sobre tuplas y tuples con nombre. Cuando necesitas un contrato de respuesta estricto con tipos y anidamiento, PostgreSQL con jsonb_build_object mas jsonb_agg sigue siendo la herramienta mas directa y predecible.