Lección 3.6 · Tiempo de lectura: ~8 min

Las funciones JSON permiten que una consulta mire dentro de un documento JSON guardado en una columna: leer un valor por su ruta, filtrar filas por él, contar los elementos de un array o convertir filas en JSON para una aplicación. En esta lección leerás valores con JSON_VALUE, JSON_EXTRACT y el operador ->>, filtrarás por ellos, trabajarás con arrays y construirás JSON con JSON_OBJECT y JSON_ARRAYAGG. Los ejemplos se ejecutan en varias bases de datos del sitio, porque cada base de datos llama a estas funciones de forma algo distinta.

Funciones JSON en SQL

JSON en una columna de la tabla

A veces una fila tiene atributos que cambian de una fila a otra, y la tabla los guarda en un único documento JSON. La tabla machine de la base de datos SQLite guarda así sus detalles:

MySQL y MariaDB tienen el tipo JSON, PostgreSQL tiene json y jsonb, SQLite y SQL Server guardan el JSON como texto. En todos ellos las funciones JSON tratan el documento como datos estructurados y no como una cadena normal.

Leer un valor por ruta

Un valor dentro del documento se indica con una ruta: $ es el documento entero, $.acquired es la clave acquired, $[0] es el primer elemento de un array, $[0].source es la clave source de ese elemento.

SQLite:

SELECT name, json_extract(details, '$.acquired') AS acquired
FROM machine;

Resultado: WY401 — 2023-05-01, Inphormex — 2021-07-15, AutoPlate 9000 — NULL. Una clave ausente da NULL, no un error.

SQLite y MySQL también tienen una forma corta, el operador ->>: details ->> '$.acquired' devuelve los mismos valores.

En MySQL y MariaDB hay dos funciones, y la diferencia importa:

SELECT JSON_EXTRACT('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS extracted,
       JSON_VALUE('{ "title": "ACADEMY DINOSAUR", "year": 2006}', '$.title') AS value;

Resultado: extracted es "ACADEMY DINOSAUR" con las comillas, sigue siendo un fragmento de JSON; value es simplemente ACADEMY DINOSAUR.

JSON_EXTRACT devuelve JSON, así que para compararlo con una cadena se envuelve en JSON_UNQUOTE. JSON_VALUE devuelve directamente un valor normal; SQL Server usa la misma función.

Filtrar por un valor dentro del JSON

Una función JSON se puede usar en WHERE como cualquier otra expresión. Como una clave ausente da NULL, IS NULL e IS NOT NULL encuentran los documentos sin la clave y con ella.

SQLite:

SELECT name, json_extract(details, '$.refurbished') AS refurbished
FROM machine
WHERE json_extract(details, '$.refurbished') IS NOT NULL;

Resultado: una máquina, Inphormex, reacondicionada el 2023-10-22.

Arrays JSON

En la base de datos University (MariaDB), research_projects.funding guarda un array de subvenciones: [{ "source": "Private Foundation", "amount": 244542.96, ...}], o [] en un proyecto sin financiación. JSON_LENGTH cuenta los elementos, y una ruta con índice lee uno de ellos:

SELECT project_id, JSON_LENGTH(funding) AS grants,
       JSON_UNQUOTE(JSON_EXTRACT(funding, '$[0].source')) AS first_source
FROM research_projects
ORDER BY project_id
LIMIT 3;

Resultado: el proyecto 1 tiene 1 subvención de Private Foundation; los proyectos 2 y 3 tienen 0, y su primera fuente es NULL.

Para comprobar si algún elemento de un array tiene un valor, MySQL y MariaDB tienen JSON_SEARCH, que devuelve la ruta del valor encontrado o NULL:

SELECT COUNT(*) AS nasa_projects
FROM research_projects
WHERE JSON_SEARCH(funding, 'one', 'NASA', NULL, '$[*].source') IS NOT NULL;

Resultado: 30 proyectos tienen una subvención de la NASA. `$[].source` significa «el source de cada elemento».*

PostgreSQL: los operadores -> y ->>

PostgreSQL lee JSON con operadores en lugar de rutas: -> devuelve un valor JSON, ->> devuelve texto. En la base de datos Bookings, los nombres de los aeropuertos se guardan en varios idiomas como jsonb, por ejemplo { "en": "Yakutsk Airport", "ru": "Якутск"}:

SELECT airport_code, airport_name ->> 'en' AS name, city ->> 'en' AS city
FROM airports_data
ORDER BY airport_code
LIMIT 3;

Resultado: AAQ — Anapa Vityazevo Airport, Anapa; ABA — Abakan Airport, Abakan; AER — Sochi International Airport, Sochi.

Construir JSON a partir de filas

La tarea contraria es devolver un resultado como JSON, por ejemplo para una aplicación web. JSON_OBJECT construye un objeto a partir de pares de clave y valor.

MySQL, Sakila:

SELECT JSON_OBJECT('id', film_id, 'title', title, 'rating', rating) AS film
FROM film
ORDER BY film_id
LIMIT 2;

Resultado: { "id": 1, "title": "ACADEMY DINOSAUR", "rating": "PG"} y { "id": 2, "title": "ACE GOLDFINGER", "rating": "G"}.

JSON_ARRAYAGG es una función de agregación: reúne los valores de muchas filas en un único array.

SELECT JSON_ARRAYAGG(name) AS categories
FROM category;

Resultado: una fila con un array de las 16 categorías: ["Action", "Animation", "Children", ...].

En PostgreSQL se hace lo mismo con json_build_object y json_agg:

SELECT json_build_object('code', airport_code, 'city', city ->> 'en') AS airport
FROM airports_data
ORDER BY airport_code
LIMIT 2;

Resultado: { "code" : "AAQ", "city" : "Anapa"} y { "code" : "ABA", "city" : "Abakan"}.

Funciones JSON en distintas bases de datos

Cuándo guardar JSON

El JSON es cómodo para atributos que cambian entre filas o que llegan de un sistema externo como un documento. Pero un valor dentro del JSON es más difícil de validar, unir e indexar que una columna. Si la misma clave está en todas las filas y se usa en filtros, merece su propia columna.

Conclusiones clave:

• Una ruta indica un valor dentro del JSON: $.key, $[0], $[0].key, $[*].key.
• JSON_VALUE (MySQL, MariaDB, SQL Server), json_extract y ->> (SQLite, MySQL) y ->> (PostgreSQL) leen un valor como un valor normal.
• JSON_EXTRACT en MySQL y MariaDB devuelve JSON: una cadena llega entre comillas y necesita JSON_UNQUOTE.
• Una clave ausente da NULL, así que IS NULL e IS NOT NULL filtran documentos por la presencia de una clave.
• JSON_OBJECT construye un objeto a partir de columnas, JSON_ARRAYAGG reúne filas en un array; PostgreSQL tiene json_build_object y json_agg.

Preguntas frecuentes

¿Qué diferencia hay entre JSON_EXTRACT y JSON_VALUE?

JSON_EXTRACT devuelve un fragmento de JSON: una cadena llega con comillas, un objeto o un array como texto JSON. JSON_VALUE devuelve un único valor escalar como texto normal, listo para comparar o mostrar.

¿Qué devuelve una función JSON cuando la clave no existe?

NULL. Por eso WHERE json_extract(details, '$.refurbished') IS NULL encuentra los documentos sin esa clave.

¿Qué diferencia hay entre -> y ->> en PostgreSQL?

-> devuelve un valor JSON, ->> devuelve texto. Usa -> para entrar más a fondo en un documento y ->> para obtener el valor final.

Preguntas de entrevista

¿Cuándo guardarías datos en una columna JSON en lugar de columnas separadas?

Cuando los atributos cambian de una fila a otra, rara vez se usan en filtros o llegan como un documento ya hecho de otro sistema. Los valores que tiene cada fila y que se usan en filtros, uniones o restricciones deben ser columnas normales.

¿Cómo encuentras las filas cuyo array JSON contiene un valor?

En MySQL y MariaDB con JSON_SEARCH(doc, 'one', 'value', NULL, '$[*].key') IS NOT NULL o JSON_CONTAINS; en PostgreSQL con el operador de contención @> sobre jsonb.

¿Cómo devuelves el resultado de una consulta como JSON?

Construye objetos con JSON_OBJECT (json_build_object en PostgreSQL) y reúnelos en un array con JSON_ARRAYAGG (json_agg); en SQL Server se añade FOR JSON PATH a la consulta.