JSONB avanzado y SQL/JSON path

PostgreSQL fue uno de los primeros motores en tratar JSON como tipo de primera clase. Una vez interiorizados los operadores básicos ->, ->> y @>, el siguiente nivel pasa por dominar el lenguaje SQL/JSON path del estándar SQL:2016, las funciones de modificación seguras y la agregación, sin perder de vista las trampas que oculta el manejo de null.

SQL/JSON path: la sintaxis estándar

El lenguaje JSON path apareció en PostgreSQL 12 con un parser nativo que evalúa expresiones contra documentos JSONB. La sintaxis comienza con $ (raíz) y permite navegar, filtrar y proyectar:

SELECT jsonb_path_query(
    '{"productos": [
        {"nombre": "Laptop", "precio": 1200, "stock": 5},
        {"nombre": "Mouse", "precio": 25, "stock": 100},
        {"nombre": "Teclado", "precio": 80, "stock": 50}
    ]}'::JSONB,
    '$.productos[*] ? (@.precio > 50)'
);
-- {"nombre": "Laptop", "precio": 1200, "stock": 5}
-- {"nombre": "Teclado", "precio": 80, "stock": 50}

Los elementos clave de la expresión:

• $ representa el documento raíz.
• .productos accede a la propiedad.
• [*] itera sobre todos los elementos del array.
• ?(@.precio > 50) aplica un filtro, donde @ es el elemento actual.

Es la misma sintaxis que verás en MongoDB, Oracle 21c y SQL Server 2022. Aprenderla es portable entre motores.

Operadores @? y @@

Para comprobar si un path existe o si un predicado se cumple sin recuperar los valores se usan dos operadores específicos:

• @? devuelve true si el path encuentra al menos una coincidencia.
• @@ evalúa una expresión booleana de path y devuelve el resultado.

SELECT jsonb '{"a": [1, 2, 3]}' @? '$.a[*] ? (@ > 2)';
-- true

SELECT jsonb '{"productos": [{"precio": 100}]}' @@ '$.productos[*].precio > 50';
-- true

Estos operadores son los recomendados para filtros en WHERE y son indexables con GIN usando la opción jsonb_path_ops:

CREATE INDEX idx_pedidos_payload_gin
    ON pedidos USING GIN (payload jsonb_path_ops);

EXPLAIN ANALYZE
SELECT * FROM pedidos
WHERE payload @? '$.lineas[*] ? (@.cantidad > 10)';

Modificación segura con jsonb_set y jsonb_insert

Modificar un documento JSONB sin perder el resto requiere las funciones jsonb_set y jsonb_insert. La diferencia clave: jsonb_set reemplaza o crea, mientras que jsonb_insert inserta sin sobrescribir.

UPDATE clientes
SET preferencias = jsonb_set(
    preferencias,
    '{notificaciones,email}',
    'true'::jsonb,
    true                            -- create_missing
)
WHERE id = 42;

El path '{notificaciones,email}' es un array de claves. El cuarto argumento (create_missing) controla si se crea la ruta cuando no existe.

UPDATE clientes
SET preferencias = jsonb_insert(
    preferencias,
    '{tags, 0}',
    '"vip"'::jsonb,
    false                           -- insert_after = false (insert before)
)
WHERE id = 42;

Para eliminar claves se usa el operador -:

UPDATE clientes
SET preferencias = preferencias - 'campo_obsoleto' - '{notificaciones,sms}';

El operador - con string elimina una clave de primer nivel; con array de strings elimina una ruta. Es la operación inversa de jsonb_set.

Agregación: jsonb_agg y jsonb_object_agg

Cuando necesitas construir un documento JSON a partir de filas, las funciones de agregación son la herramienta correcta. Devuelven un array (jsonb_agg) o un objeto (jsonb_object_agg):

SELECT
    cliente_id,
    jsonb_agg(
        jsonb_build_object(
            'pedido_id', id,
            'total', total,
            'fecha', creado_en
        )
        ORDER BY creado_en DESC
    ) AS pedidos
FROM pedidos
WHERE cliente_id IN (SELECT id FROM clientes WHERE activo)
GROUP BY cliente_id;

La cláusula ORDER BY dentro de la agregación define el orden de los elementos en el array resultante. Sin ella, el orden no está garantizado.

SELECT
    categoria,
    jsonb_object_agg(nombre, precio) AS precios
FROM productos
GROUP BY categoria;
-- {"categoria": "perifericos", "precios": {"Mouse": 25, "Teclado": 80}}

jsonb_object_agg(clave, valor) produce un objeto donde cada fila aporta una propiedad. Útil para diccionarios y mapas precomputados.

El null JSON frente al NULL SQL

PostgreSQL distingue dos tipos de "nulo" cuando trabajas con JSONB y esto causa errores sutiles:

SELECT
    jsonb_typeof('null'::jsonb) AS json_null,
    jsonb_typeof(NULL::jsonb)   AS sql_null;
-- json_null = 'null'
-- sql_null  = NULL

• El null JSON es un valor concreto del documento; el path $.campo devuelve 'null'::jsonb.
• El NULL SQL representa la ausencia de valor en la columna SQL.

Esta distinción importa en WHERE:

-- Documentos donde la propiedad existe pero su valor es null JSON
SELECT * FROM productos WHERE atributos -> 'descuento' = 'null'::jsonb;

-- Documentos donde la propiedad NO existe en absoluto
SELECT * FROM productos WHERE NOT (atributos ? 'descuento');

-- Documentos donde la columna entera es NULL SQL
SELECT * FROM productos WHERE atributos IS NULL;

Esta diferencia es la fuente número uno de bugs en migraciones desde MongoDB. Define en tu equipo la convención: o bien no se guardan null JSON, o bien se procesan explícitamente.

Stripping de null y normalización

PostgreSQL ofrece jsonb_strip_nulls para eliminar todas las propiedades cuyo valor es null JSON, recursivamente. Útil para normalizar documentos que llegan desde APIs ruidosas:

INSERT INTO eventos(payload)
SELECT jsonb_strip_nulls(p) FROM datos_externos;

Para sanitizar arrays con elementos repetidos puedes combinar jsonb_array_elements con DISTINCT y reagregar:

SELECT jsonb_agg(DISTINCT v ORDER BY v)
FROM jsonb_array_elements_text('["a", "b", "a", "c"]'::jsonb) AS v;
-- ["a", "b", "c"]

Indexación selectiva con índices funcionales

Cuando todas las consultas filtran por un campo concreto del JSONB, un índice GIN sobre todo el documento es excesivo. Un índice B-tree funcional sobre la expresión es más pequeño y rápido:

CREATE INDEX idx_eventos_tipo
    ON eventos ((payload->>'tipo'));

CREATE INDEX idx_eventos_user_tipo
    ON eventos ((payload->>'user_id'), (payload->>'tipo'))
    WHERE (payload->>'tipo') IN ('login', 'purchase');

El índice parcial combinado con funcional es la forma más eficiente de cubrir queries específicas sobre JSONB sin pagar el coste de un GIN completo.

Arquitectura: cuándo JSONB y cuándo columnas

JSONB no es una bala de plata. La regla práctica:

• Atributos relacionales y consultados con frecuencia: columnas tipadas.
• Atributos heterogéneos por tenant o producto: JSONB.
• Datos de log o eventos sin esquema fijo: JSONB.
• Validaciones de integridad referencial: columnas y foreign keys, no JSONB.

flowchart TB
    A[Decision: tipo de campo] --> B{Esquema fijo?}
    B -->|Si, todos lo tienen| C[Columna tipada]
    B -->|No, varia por fila| D{Consultado con WHERE?}
    D -->|Si, con filtros estables| E[JSONB + indice funcional]
    D -->|Si, con filtros dinamicos| F[JSONB + GIN jsonb_path_ops]
    D -->|No, solo se lee como bloque| G[JSONB sin indice]

JSONB con SQL/JSON path convierte a PostgreSQL en una base de datos documental cuando lo necesitas, sin renunciar a transacciones, foreign keys, joins ni el resto del modelo relacional.