Uso de JSON en la base de datos Oracle Database

1) Introducción

Oracle Database ofrece soporte nativo para JSON desde la versión 12c (2014).

  • Se puede guardar JSON en columnas de tipo VARCHAR2, CLOB, BLOB.
  • Desde Oracle 21c existe un tipo de dato específico JSON.
  • Oracle asegura que el contenido sea JSON válido con CHECK ... IS JSON.
  • Incluye funciones y operadores para consultar, modificar y generar JSON.

2) Crear tabla con columna JSON

CREATE TABLE productos (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    nombre VARCHAR2(100),
    datos CLOB CHECK (datos IS JSON)
);

Con IS JSON, Oracle valida que la columna contenga JSON válido.

3) Insertar documentos JSON

INSERT INTO productos (nombre, datos) VALUES
('Teclado', '{"codigo":101,"precio":1200.50,"disponible":true,"tags":["periférico","oferta"]}');

INSERT INTO productos (nombre, datos) VALUES
('Mouse', '{"codigo":102,"precio":850.75,"disponible":false}');

4) Consultar JSON

Oracle permite consultar con JSON_VALUE, JSON_QUERY y dot-notation (21c+). Docs: JSON en Oracle.

Extraer valores escalares (JSON_VALUE)

SELECT 
    nombre,
    JSON_VALUE(datos, '$.codigo') AS codigo,
    JSON_VALUE(datos, '$.precio') AS precio
FROM productos;

Extraer objetos/arrays (JSON_QUERY)

SELECT JSON_QUERY(datos, '$.tags') AS tags
FROM productos
WHERE nombre = 'Teclado';
-- Resultado: ["periférico", "oferta"]

5) Consultar con condiciones

SELECT nombre, JSON_VALUE(datos, '$.precio') AS precio
FROM productos
WHERE JSON_VALUE(datos, '$.precio') > 1000;

6) Actualizar JSON

Con JSON_MERGEPATCH se modifican documentos JSON:

UPDATE productos
SET datos = JSON_MERGEPATCH(datos, '{"precio": 900}')
WHERE nombre = 'Mouse';

Con JSON_TRANSFORM (21c+):

UPDATE productos
SET datos = JSON_TRANSFORM(datos, SET '$.stock' = 50)
WHERE nombre = 'Teclado';

7) Eliminar campos JSON

UPDATE productos
SET datos = JSON_TRANSFORM(datos, REMOVE '$.tags')
WHERE nombre = 'Teclado';

8) Consultar arrays JSON

Extraer elementos individuales con JSON_TABLE:

SELECT t.nombre, jt.value AS tag
FROM productos t,
     JSON_TABLE(t.datos, '$.tags[*]' COLUMNS (value VARCHAR2(50) PATH '$')) jt
WHERE t.nombre = 'Teclado';
-- Resultado: Teclado | periférico  /  Teclado | oferta

9) Generar JSON desde consultas SQL

SELECT JSON_OBJECT(
    'id' VALUE id,
    'nombre' VALUE nombre,
    'datos' VALUE datos
) AS producto_json
FROM productos;
-- También existe JSON_ARRAYAGG para crear arrays.

10) Índices sobre JSON

Oracle soporta functional indexes para acelerar consultas sobre JSON.

CREATE INDEX idx_precio_json 
ON productos (JSON_VALUE(datos, '$.precio'));

-- Consultas por precio serán más rápidas.

11) Ejemplo avanzado: pedidos con JSON

CREATE TABLE pedidos (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cliente VARCHAR2(100),
    detalle CLOB CHECK (detalle IS JSON)
);

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 con JSON_TABLE
SELECT p.cliente, i.producto, i.cantidad, i.precio
FROM pedidos p,
     JSON_TABLE(p.detalle, '$.items[*]'
        COLUMNS (
          producto VARCHAR2(50) PATH '$.producto',
          cantidad NUMBER PATH '$.cantidad',
          precio   NUMBER PATH '$.precio'
        )
     ) i;

12) Ventajas y desventajas del JSON en Oracle

Ventajas

  • Validación automática de JSON (IS JSON).
  • Funciones muy poderosas (JSON_VALUE, JSON_QUERY, JSON_TABLE).
  • Se puede generar JSON desde SQL (JSON_OBJECT, JSON_ARRAYAGG).
  • Soporte a índices JSON para mejorar rendimiento.
  • Combinación ideal: SQL relacional + documentos JSON.

Desventajas

  • Mayor complejidad en comparación con bases NoSQL puras (ej.: MongoDB).
  • Requiere versión reciente (12c+ para JSON, 21c para tipo JSON).
  • Puede consumir más recursos que datos normalizados.

Resumen

  • Oracle soporta JSON desde la versión 12c y mejora en 21c con el tipo JSON.
  • Columnas con CHECK ... IS JSON garantizan integridad.
  • Funciones principales: JSON_VALUE (escalares), JSON_QUERY (objetos/arrays), JSON_TABLE (tablas de JSON), JSON_MERGEPATCH/JSON_TRANSFORM (modificar documentos).
  • Permite índices en expresiones JSON.
  • Ideal para mezclar la potencia de SQL con la flexibilidad de JSON.
Retornar