PostgreSQL y JSONB: el poder de una DB relacional con la flexibilidad de documentos

La pregunta “¿SQL o NoSQL?” perdió relevancia cuando PostgreSQL adquirió un soporte robusto para documentos JSON. Con JSONB tienes un esquema estricto donde lo necesitas y flexibilidad de documentos donde lo requieres, en la misma base de datos.

Tabla de contenidos

JSON vs JSONB: usa siempre JSONB

-- JSON: almacena el texto literal tal cual
-- JSONB: almacena en formato binario procesado

-- Ventajas de JSONB:
-- ✓ Soporta índices GIN (consultas ultra-rápidas)
-- ✓ Elimina espacios en blanco redundantes y claves duplicadas
-- ✓ Operadores de contención: @>, <@
-- ✗ Ligeramente más lento al escribir (parsing)
-- ✗ No preserva el orden de las claves ni espacios
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  type       TEXT NOT NULL,
  timestamp  TIMESTAMPTZ DEFAULT NOW(),
  payload    JSONB NOT NULL,             
  metadata   JSONB DEFAULT '{}'::JSONB
);

Inserción y consultas básicas

-- Insertar un evento con payload flexible
INSERT INTO events (type, payload) VALUES
  ('user.register', '{"name": "Ana Garcia", "plan": "pro", "country": "MX"}'),
  ('payment.completed', '{"amount": 99.99, "currency": "USD", "method": "card"}'),
  ('error.api',        '{"code": 429, "endpoint": "/api/v2/items", "ip": "10.0.0.1"}');

-- Extracción de campos: operador ->>
SELECT payload->>'name' AS name
FROM events
WHERE type = 'user.register';

-- Extracción anidada
SELECT payload->'address'->>'city' AS city
FROM events
WHERE type = 'user.register';

-- Filtrar por valor dentro de JSON
SELECT * FROM events
WHERE type = 'payment.completed'
  AND (payload->>'amount')::NUMERIC > 50;

Índices GIN: consultas sobre JSON a velocidad SQL

-- Índice GIN sobre toda la columna JSONB
CREATE INDEX idx_events_payload ON events USING GIN (payload);  

-- Índice sobre una clave específica (más eficiente)
CREATE INDEX idx_events_payment_type ON events
  USING GIN ((payload->'method'));

-- Ahora estas consultas usan el índice:
SELECT * FROM events
WHERE payload @> '{"plan": "pro"}';      -- contiene este objeto

SELECT * FROM events
WHERE payload ? 'code';                -- tiene esta clave

Operadores de contención

-- @>  "contiene"
SELECT * FROM events
WHERE payload @> '{"currency": "USD", "method": "card"}';

-- <@  "está contenido en"
SELECT '{"a": 1}'::JSONB <@ '{"a": 1, "b": 2}'::JSONB;  -- true

-- ?   "tiene la clave"
SELECT * FROM events WHERE payload ? 'code';

-- ?|  "tiene cualquiera de las claves"
SELECT * FROM events WHERE payload ?| ARRAY['name', 'email'];

-- ?&  "tiene todas las claves"
SELECT * FROM events WHERE payload ?& ARRAY['amount', 'currency'];

jsonb_set y actualizaciones parciales

Una ventaja enorme sobre los documentos puros: actualizas un campo sin reescribir todo el documento.

-- Actualizar un campo dentro de JSONB
UPDATE events
SET payload = jsonb_set(payload, '{plan}', '"enterprise"')  
WHERE type = 'user.register'
  AND payload->>'name' = 'Ana Garcia';

-- Eliminar una clave
UPDATE events
SET payload = payload - 'ip'
WHERE type = 'error.api';

-- Añadir una entrada a un array dentro de JSONB
UPDATE events
SET payload = jsonb_insert(payload, '{tags, -1}', '"urgent"')
WHERE type = 'error.api';

Funciones de agregación: jsonb_agg y jsonb_object_agg

-- Agrupar pagos por moneda como un array JSON
SELECT
  payload->>'currency' AS currency,
  COUNT(*)           AS total_payments,
  jsonb_agg(payload) AS detail          
FROM events
WHERE type = 'payment.completed'
GROUP BY currency;

-- Construir un objeto a partir de filas
SELECT jsonb_object_agg(type, COUNT(*))  
FROM events
GROUP BY 1;

Esquema híbrido: lo mejor de ambos mundos

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT UNIQUE NOT NULL,
  name        TEXT NOT NULL,
  price       NUMERIC(10,2) NOT NULL,
  category    TEXT NOT NULL,
  -- Campos estructurados ↑ para JOINs, índices B-tree, constraints
  attributes  JSONB DEFAULT '{}',
  -- Atributos flexibles ↓ según la categoría del producto
  CHECK (price > 0)
);

-- Electrónica: { "voltage": 220, "warranty_months": 24 }
-- Ropa:        { "sizes": ["S","M","L"], "material": "cotton" }
-- Libros:      { "isbn": "...", "pages": 320 }

-- Consulta que aprovecha ambos tipos de columnas
SELECT name, attributes->>'warranty_months' AS warranty
FROM products
WHERE category = 'electronics'
  AND (attributes->>'warranty_months')::INT >= 12
  AND price < 500;

JSONB no sustituye a las columnas tipadas para campos críticos. La regla: si vas a hacer un JOIN, WHERE o ORDER BY frecuente sobre un campo, conviértelo en columna. Si son metadatos variables o que se consultan raramente, ponlo en JSONB.

Traducido del original (Andrés Ujpán)