PostgreSQL
JSON, JSONB y modelado híbrido
Uso de JSON y JSONB para datos semiestructurados, operadores, índices GIN, validación y límites frente al modelado relacional.
- Última actualización
- Actualizada
- Nivel
- Aplicación
PostgreSQL
Uso de JSON y JSONB para datos semiestructurados, operadores, índices GIN, validación y límites frente al modelado relacional.
PostgreSQL ofrece json y jsonb:
json
→ conserva el texto validado casi como llegó
→ vuelve a procesarlo al consultar
jsonb
→ almacena una representación binaria normalizada
→ facilita operadores e índices
→ no conserva whitespace, orden de keys ni duplicados como contratoEn la mayoría de aplicaciones que necesitan consultar el documento, jsonb es la opción práctica. json aporta cuando el texto original importa y rara vez se inspecciona.
No todo dato tiene una estructura estable controlada por tu sistema:
Obligar cada key a convertirse inmediatamente en una columna puede producir migraciones constantes. Pero guardar toda la entidad como documento elimina integridad y hace más costosas las consultas relacionales.
Ejemplo de orden:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
business_id bigint NOT NULL REFERENCES businesses(id),
customer_id bigint NOT NULL REFERENCES customers(id),
status text NOT NULL,
total_amount numeric(14,2) NOT NULL,
provider_metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);Las columnas relacionales representan el contrato central. provider_metadata guarda datos opcionales de un tercero.
Regla práctica:
participa en joins, filtros frecuentes, constraints, permisos o reporting
→ columna tipada
estructura variable, externa o poco consultada
→ JSONB candidatoSELECT
provider_metadata->'shipping' AS shipping_json,
provider_metadata->'shipping'->>'carrier' AS carrier_text
FROM orders;-> devuelve JSON/JSONB.->> devuelve text.#> accede mediante path y devuelve JSON.#>> devuelve text.Cuando el valor debe compararse numéricamente, haz cast explícito y valida el shape:
(provider_metadata->>'attempts')::integerSi una fila contiene texto inválido, el cast falla. No conviertas datos no confiables sin estrategia de validación.
WHERE provider_metadata @> '{"channel":"mobile"}'::jsonb@> pregunta si el documento contiene la estructura indicada.
WHERE provider_metadata ? 'channel'
WHERE provider_metadata ?| ARRAY['channel', 'source']
WHERE provider_metadata ?& ARRAY['channel', 'source']Los operadores exactos y su soporte de índice dependen de la operator class.
WHERE provider_metadata @? '$.items[*] ? (@.quantity > 10)'JSONPath permite condiciones complejas, navegación y manejo estructurado. Debe probarse con documentos faltantes, arrays vacíos, tipos incorrectos y versión del servidor.
CREATE INDEX orders_metadata_gin_idx
ON orders USING gin (provider_metadata);La operator class default soporta una familia amplia de operadores.
Alternativa:
CREATE INDEX orders_metadata_path_gin_idx
ON orders USING gin (provider_metadata jsonb_path_ops);jsonb_path_ops suele ser más compacto y específico para containment, pero soporta menos operadores.
Trade-offs de GIN:
No crees GIN automáticamente por existir JSONB.
Si solo consultas una ruta:
CREATE INDEX orders_provider_name_idx
ON orders ((provider_metadata->>'provider'));Para filtro por tenant y provider:
CREATE INDEX orders_business_provider_idx
ON orders (business_id, (provider_metadata->>'provider'));Un expression index es más pequeño y directo que indexar el documento completo, pero acopla el esquema físico a esa ruta.
Un campo JSON estable puede promocionarse:
ALTER TABLE orders
ADD COLUMN external_order_id text
GENERATED ALWAYS AS (provider_metadata->>'externalOrderId') STORED;
CREATE UNIQUE INDEX orders_external_id_uidx
ON orders(business_id, external_order_id)
WHERE external_order_id IS NOT NULL;Esto permite tipos, constraints e índices visibles sin duplicación manual. Aun así, el valor fuente debe cumplir el contrato.
Ejemplos básicos:
CHECK (jsonb_typeof(provider_metadata) = 'object')CHECK (
NOT (provider_metadata ? 'attempts')
OR jsonb_typeof(provider_metadata->'attempts') = 'number'
)Constraints complejas son difíciles de leer y evolucionar. Para un contrato central, una columna o tabla suele ser mejor.
UPDATE orders
SET provider_metadata = jsonb_set(
provider_metadata,
'{shipping,carrier}',
to_jsonb($1::text),
true
)
WHERE id = $2;Aunque cambie una key, PostgreSQL crea una nueva versión de fila y normalmente un nuevo valor JSONB. En documentos grandes con updates frecuentes existe write amplification, TOAST churn y WAL significativo.
Para eliminar una key:
provider_metadata - 'temporaryKey'Para paths:
provider_metadata #- '{shipping,debug}'provider_metadata || jsonb_build_object('source', $1)En objetos, keys de la derecha reemplazan las de la izquierda en el nivel superior. No realiza merge profundo automático.
SELECT item
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.provider_metadata->'items') AS item;Expandir arrays multiplica filas. Si cada item tiene identidad, FK, precio, cantidad y consultas propias, debe vivir en order_items, no dentro del documento.
Existen dos conceptos:
SQL NULL
→ no existe valor de columna
JSON null
→ el documento contiene literal nullEjemplo:
provider_metadata IS NULL
provider_metadata->'key' = 'null'::jsonbTambién una key ausente devuelve SQL NULL al acceder. Distingue ausencia, JSON null y string "null".
Input:
{"a":1,"a":2}En JSONB queda una única key efectiva. No uses JSONB para conservar firmas basadas en bytes, orden de propiedades o payload original. Guarda el raw body separado cuando la evidencia exacta importa.
Un evento puede tener:
CREATE TABLE events (
id uuid PRIMARY KEY,
event_type text NOT NULL,
occurred_at timestamptz NOT NULL,
aggregate_id uuid NOT NULL,
payload jsonb NOT NULL,
schema_version integer NOT NULL
);Las columnas externas permiten buscar y ordenar. payload conserva datos específicos del tipo. schema_version facilita evolución y consumidores.
Si tenant_id está solo dentro de JSONB:
Mantén el tenant como columna tipada y usa JSONB solo para metadata.
Puede almacenarse en TOAST. Leer una key puede requerir detoast; actualizaciones reescriben mucho dato.
Una misma key como number en unas filas y string en otras rompe casts e índices semánticos.
Las queries e índices antiguos no migran automáticamente. Usa expand-contract.
El coste puede superar el beneficio de lectura.
Valida tamaño, profundidad y shape antes de persistir para evitar abuso y datos imposibles de consultar.
jsonb_set actualiza in-place.Arrays, ranges, domains y tipos avanzados muestra cuándo los tipos nativos expresan mejor el dominio que texto o JSONB.