Curso sobre Funciones JSON en SQL

Las funciones JSON en SQL permiten manipular y extraer información de datos almacenados en formato JSON. Estas funciones son útiles cuando trabajamos con bases de datos que almacenan estructuras semiestructuradas. A continuación, veremos las funciones más utilizadas en MySQL y PostgreSQL, ya que SQLite tiene soporte limitado para JSON.

1. JSON_EXTRACT() - Extraer Datos desde JSON

JSON_EXTRACT() se usa para obtener valores de un campo JSON en MySQL y PostgreSQL (como -> y ->> en PostgreSQL).

Ejemplo:

1SELECT JSON_EXTRACT('{"nombre": "Juan", "edad": 30}', '$.nombre'); -- Resultado: "Juan"
2
3-- También puedes usar el alias corto:
4SELECT '$.nombre' -> '$.nombre'; -- en versiones modernas

1-- `->` devuelve un valor JSON.
2SELECT '{"nombre": "Juan", "edad": 30}'::json->'nombre';
3
4-- `->>` devuelve el valor como texto.
5SELECT '{"nombre": "Juan", "edad": 30}'::json->>'nombre';

2. JSON_OBJECT() y JSON_BUILD_OBJECT() - Crear un Objeto JSON

Estas funciones permiten construir un JSON dinámicamente desde columnas.

Ejemplo:

1SELECT JSON_OBJECT('nombre', nombre, 'edad', edad) FROM empleados;

3. JSON_ARRAY() y JSON_BUILD_ARRAY() - Crear un Array JSON

Permiten crear arrays de valores JSON a partir de múltiples datos.

Ejemplo:

1SELECT JSON_ARRAY('Juan', 'María', 'Carlos');

4. JSON_CONTAINS() - Buscar Valores en JSON (Solo en MySQL)

Permite verificar si un JSON contiene un valor específico.

Ejemplo:

1SELECT JSON_CONTAINS('["rojo", "azul", "verde"]', '"azul"');  -- Resultado: 1

Esto devuelve 1 si el valor está presente y 0 si no lo está.

5. JSONB_EXISTS() - Buscar Valores en JSONB (Solo en PostgreSQL)

Para PostgreSQL, cuando se usa JSONB, se puede verificar si un campo JSON contiene una clave específica.

1SELECT nombre, datos
2FROM empleados
3WHERE JSONB_EXISTS(datos, 'edad');

5b. JSON_CONTAINS_PATH() - Verificar si una clave existe dentro de un objeto JSON

1SELECT JSON_CONTAINS_PATH('{"nombre": "Juan", "edad": 30}', 'one', '$.nombre');

6. JSON_MERGE_PATCH() - Fusionar JSON (Solo en MySQL)

Permite combinar dos objetos JSON en MySQL.

Ejemplo:

1SELECT JSON_MERGE_PATCH('{"nombre": "Juan"}', '{"edad": 30}');

Esto devuelve {"nombre": "Juan", "edad": 30}.

7. JSON_SET() - Modificar Valores en JSON

Permite actualizar valores dentro de un objeto JSON sin reescribir todo el contenido.

Ejemplo:

1SELECT JSON_SET('{"nombre": "Juan", "edad": 30}', '$.edad', 31);

8. JSON_REMOVE() - Eliminar Valores en JSON (Solo en MySQL)

Permite eliminar una clave específica dentro de un objeto JSON.

Ejemplo:

1SELECT JSON_REMOVE('{"nombre": "Juan", "edad": 30}', '$.edad');

Esto devuelve {"nombre": "Juan"}.

9. JSON_UNQUOTE() - Eliminar comillas JSON

Equivalente a "quitar las comillas de un string JSON"

Ejemplo:

1SELECT JSON_UNQUOTE('"Juan"');            -- Juan
2SELECT JSON_UNQUOTE(JSON_EXTRACT('{"nombre":"Juan"}', '$.nombre'));

10. JSON_LENGTH() - Devuelve el número de elementos

Devuelve el número de elementos de:

Un array JSON
Un objeto JSON (cuenta sus claves)

Ejemplo:

1SELECT JSON_LENGTH('["rojo", "verde", "azul"]');       -- Resultado: 3
2SELECT JSON_LENGTH('{"a":1,"b":2,"c":3}');              -- Resultado: 3
3SELECT JSON_LENGTH('{"persona": {"nombre": "Ana"}}', '$.persona'); -- 1

Modelado físico avanzado relacional de bases de datos