Uso de JSON en la base de datos PostgreSQL

1) Introducción

PostgreSQL es una de las bases de datos relacionales que mejor soporte nativo tiene para JSON.

• Desde la versión 9.2 — soporte de tipo JSON.
• Desde la versión 9.4 — introducción de JSONB (JSON Binary), mucho más eficiente.

2) Crear una tabla con JSONB

CREATE TABLE productos (
    id SERIAL PRIMARY KEY,
    nombre VARCHAR(100),
    datos JSONB
);

3) Insertar documentos JSON

INSERT INTO productos (nombre, datos) VALUES
('Teclado', '{"codigo":101, "precio":1200.50, "disponible":true, "tags":["periférico","oferta"]}'),
('Mouse',   '{"codigo":102, "precio":850.75, "disponible":false}');
-- PostgreSQL valida automáticamente que los datos sean JSON bien formado.

4) Consultar datos JSON

Extraer un campo con -> y ->>

• -> devuelve JSON.
• ->> devuelve texto.

SELECT 
    nombre,
    datos->'codigo'   AS codigo_json,
    datos->>'codigo'  AS codigo_texto
FROM productos;

5) Consultar con condiciones

-- Productos con precio > 1000
SELECT nombre, datos->>'precio' AS precio
FROM productos
WHERE (datos->>'precio')::DECIMAL > 1000;

6) Actualizar valores dentro de JSON

Usando jsonb_set

-- Cambiar precio del Mouse
UPDATE productos
SET datos = jsonb_set(datos, '{precio}', '900.00'::jsonb)
WHERE nombre = 'Mouse';

Eliminar campos con -

-- Eliminar el campo "tags" del Teclado
UPDATE productos
SET datos = datos - 'tags'
WHERE nombre = 'Teclado';

Agregar un nuevo campo

UPDATE productos
SET datos = jsonb_set(datos, '{stock}', '50'::jsonb)
WHERE nombre = 'Teclado';

7) Consultar dentro de arrays JSON

-- Extraer el primer tag del Teclado
SELECT datos->'tags'->>0 AS primer_tag
FROM productos
WHERE nombre = 'Teclado';

8) Funciones JSON en PostgreSQL

• jsonb_set(doc, path, new_value) — modifica o agrega un campo.
• jsonb_insert(doc, path, new_value) — inserta en una posición específica.
• jsonb_array_elements(array) — expande un array en filas.
• jsonb_each(doc) — devuelve pares clave-valor.
• jsonb_object_keys(doc) — lista las claves de un objeto.
• jsonb_agg(valores) — agrupa resultados como un array JSON.
• row_to_json / json_build_object — convierte filas SQL a JSON.

-- Expandir un array
SELECT jsonb_array_elements_text(datos->'tags') AS tag
FROM productos
WHERE nombre = 'Teclado';
-- Resultado: periférico, oferta

9) Índices para JSON

PostgreSQL permite indexar campos JSONB usando GIN (Generalized Inverted Index).

CREATE INDEX idx_productos_datos ON productos USING GIN (datos);

-- Consulta optimizada
SELECT * FROM productos
WHERE datos @> '{"disponible": true}';
-- El operador @> significa "contiene".

10) Ejemplo avanzado: pedidos con JSONB

CREATE TABLE pedidos (
    id SERIAL PRIMARY KEY,
    cliente VARCHAR(100),
    detalle JSONB
);

INSERT INTO pedidos (cliente, detalle) VALUES
('María', '{
  "items": [
    {"producto": "Notebook", "cantidad": 1, "precio": 2500.00},
    {"producto": "Mouse", "cantidad": 2, "precio": 850.75}
  ],
  "total": 4201.50
}');

-- Consultar todos los items
SELECT jsonb_array_elements(detalle->'items') AS item
FROM pedidos
WHERE cliente = 'María';

-- Solo la cantidad del primer producto
SELECT detalle->'items'->0->>'cantidad' AS cantidad
FROM pedidos
WHERE cliente = 'María';

11) Exportar resultados como JSON

SELECT json_build_object(
    'id', id,
    'cliente', cliente,
    'detalle', detalle
) AS pedido_json
FROM pedidos;

12) Ventajas y desventajas del JSON en PostgreSQL

Ventajas

• JSONB permite consultas rápidas e indexación eficiente.
• Soporte muy completo de funciones y operadores.
• Ideal para datos semiestructurados en sistemas relacionales.
• Permite mezclar SQL tradicional + flexibilidad de documentos JSON.

Desventajas

• Mayor consumo de almacenamiento que datos normalizados.
• Riesgo de esquemas caóticos si se abusa de JSON sin control.
• Puede ser más lento que MongoDB en documentos muy grandes.

Resumen

• PostgreSQL soporta JSON y JSONB, siendo este último más eficiente.
• Permite insertar, consultar, modificar y eliminar partes de JSON.
• Posee funciones avanzadas (jsonb_set, jsonb_array_elements, json_build_object).
• Se pueden crear índices GIN para búsquedas rápidas.
• Es ideal para proyectos que requieren lo mejor de SQL relacional + flexibilidad JSON.