[MySQL] Extraer valores con JSON_EXTRACT en MySQL

por
Avatar@sernastudio

Cuando trabajamos con bases de datos, puede ocurrir que tengamos información almacenada en una columna en formato JSON.

Esto es especialmente habitual cuando trabajamos con datos estructurados, configuraciones, respuestas de APIs o información que necesitamos mantener agrupada dentro de una misma columna.

Por ejemplo, imaginemos que tenemos una columna llamada ageT que almacena información como esta:

[
    {"count": 20, "age": "19"},
    {"count": 142, "age": "27"},
    {"count": 85, "age": "31"}
]

En este caso, podemos interpretar los datos de la siguiente manera:

  • 20 usuarios tienen 19 años.
  • 142 usuarios tienen 27 años.
  • 85 usuarios tienen 31 años.

Pero aquí viene la pregunta interesante:

¿Cómo podemos acceder desde MySQL únicamente al valor que nos interesa dentro de ese JSON?

Para ello podemos utilizar las funciones y operadores JSON que proporciona MySQL.

JSON en MySQL

MySQL incorpora soporte nativo para trabajar con documentos JSON, permitiéndonos consultar elementos concretos de una estructura sin necesidad de recuperar todo el contenido y procesarlo posteriormente desde PHP, JavaScript u otro lenguaje.

Una de las funciones más conocidas para ello es:

JSON_EXTRACT()

Su funcionamiento básico es sencillo: le indicamos el documento JSON y la ruta del elemento que queremos obtener.

La sintaxis general es:

JSON_EXTRACT(documento_json, '$.ruta')

El segundo parámetro es lo que se conoce como JSON Path.

¿Qué significa el símbolo $?

Cuando trabajamos con JSON Path, el símbolo $ representa la raíz del documento JSON.

Por ejemplo, si tenemos un objeto:

{
    "nombre": "Victor",
    "edad": 35
}

Podríamos obtener el nombre utilizando:

JSON_EXTRACT(datos, '$.nombre')

Y la edad:

JSON_EXTRACT(datos, '$.edad')

La idea es bastante intuitiva:

$        → raíz
$.nombre → propiedad "nombre"
$.edad   → propiedad "edad"

Cuando nuestro JSON contiene un array

Ahora vamos a complicar un poco el ejemplo.

Nuestro campo no contiene un único objeto, sino un array de objetos:

[
    {"count": 20, "age": "19"},
    {"count": 142, "age": "27"},
    {"count": 85, "age": "31"}
]

En este caso necesitamos acceder a la propiedad age de todos los elementos del array.

Para ello podemos utilizar:

JSON_EXTRACT(ageT, '$[*].age')

El elemento importante aquí es:

$[*].age

El * significa que queremos recorrer todos los elementos del array.

Por tanto:

$[*].age

significa, básicamente:

“Busca la propiedad age dentro de todos los elementos que existan en este array.”

Utilizando JSON_EXTRACT() en una consulta SQL

Supongamos que tenemos una tabla llamada table_demo con una columna ageT que almacena nuestro JSON.

Podemos hacer algo como:

SELECT
    demo_name AS 'Demo Name',
    JSON_EXTRACT(ageT, '$[*].age') AS Edad
FROM table_demo
ORDER BY demo_name;

De esta forma MySQL nos devolverá los valores de age encontrados dentro del JSON.

Dependiendo del contenido almacenado, el resultado tendrá una representación JSON, por ejemplo:

["19", "27", "31"]

Y aquí aparece una diferencia importante.

JSON_EXTRACT() devuelve JSON

JSON_EXTRACT() devuelve un valor JSON.

Esto es perfecto cuando queremos seguir trabajando con ese resultado como JSON, pero puede no ser exactamente lo que necesitamos cuando queremos obtener simplemente el texto contenido en una propiedad.

Por ejemplo:

SELECT JSON_EXTRACT('{"edad":"27"}', '$.edad');

El resultado será un valor JSON:

"27"

Si queremos obtener el valor como texto, MySQL también nos proporciona el operador:

->>

Por ejemplo:

SELECT datos->>'$.edad'
FROM usuarios;

En este caso obtendremos:

27

sin las comillas propias de la representación JSON.

La diferencia entre -> y ->>

Estos dos operadores son muy útiles y conviene conocer la diferencia.

Operador Equivalente Resultado
-> JSON_EXTRACT() Devuelve JSON
->> JSON_UNQUOTE(JSON_EXTRACT()) Devuelve el valor sin las comillas JSON

Por ejemplo:

SELECT datos->'$.edad'
FROM usuarios;

Devuelve conceptualmente:

"27"

Mientras que:

SELECT datos->>'$.edad'
FROM usuarios;

Devuelve:

27

Esta diferencia puede parecer pequeña, pero resulta bastante importante cuando posteriormente queremos utilizar ese valor en operaciones, comparaciones o en nuestra aplicación.

¿Y si queremos obtener un elemento concreto del array?

También podemos acceder a una posición concreta del array.

Recordemos nuestro ejemplo:

[
    {"count": 20, "age": "19"},
    {"count": 142, "age": "27"},
    {"count": 85, "age": "31"}
]

Si queremos obtener la edad del primer elemento podemos utilizar:

JSON_EXTRACT(ageT, '$[0].age')

El índice comienza en 0, por lo que:

$[0].age → primer elemento
$[1].age → segundo elemento
$[2].age → tercer elemento

También podemos utilizar el operador ->>:

ageT->>'$[0].age'

Un detalle importante: JSON_EXTRACT() no convierte un array en filas

Y aquí tenemos una diferencia importante respecto a lo que hacía nuestro ejemplo original.

Si hacemos:

SELECT JSON_EXTRACT(ageT, '$[*].age')
FROM table_demo;

MySQL nos devolverá el conjunto de edades como un único valor JSON por cada fila de table_demo.

Por ejemplo:

["19", "27", "31"]

Esto es perfecto si queremos enviar ese JSON a nuestra aplicación y procesarlo posteriormente.

Pero si nuestro objetivo es obtener una fila SQL por cada elemento del array, entonces necesitamos otra herramienta: JSON_TABLE().

Obtener cada elemento del JSON como una fila con JSON_TABLE()

JSON_TABLE() resulta especialmente interesante cuando queremos convertir una estructura JSON en una especie de tabla temporal que podamos consultar mediante SQL.

Por ejemplo:

SELECT jt.age, jt.count
FROM table_demo AS t
JOIN JSON_TABLE(
    t.ageT,
    '$[*]'
    COLUMNS (
        count INT PATH '$.count',
        age INT PATH '$.age'
    )
) AS jt;

Ahora podemos trabajar con cada elemento individualmente:

age | count
----+------
19  | 20
27  | 142
31  | 85

Y aquí es donde JSON empieza a resultar realmente potente dentro de SQL, porque ya podemos realizar operaciones sobre esos valores.

Por ejemplo: ordenar por edad

Una vez que hemos convertido los elementos del JSON en filas, podemos ordenar los resultados:

SELECT
    jt.age,
    jt.count
FROM table_demo AS t
JOIN JSON_TABLE(
    t.ageT,
    '$[*]'
    COLUMNS (
        count INT PATH '$.count',
        age INT PATH '$.age'
    )
) AS jt
ORDER BY jt.age DESC;

Y también podemos realizar filtros:

SELECT
    jt.age,
    jt.count
FROM table_demo AS t
JOIN JSON_TABLE(
    t.ageT,
    '$[*]'
    COLUMNS (
        count INT PATH '$.count',
        age INT PATH '$.age'
    )
) AS jt
WHERE jt.age >= 25
ORDER BY jt.age DESC;

Ahora ya no estamos simplemente “extrayendo” un valor del JSON. Estamos tratando los datos contenidos en el JSON como datos SQL.

¿Qué opción debería utilizar?

Dependerá de lo que necesitemos hacer.

  • Si necesitamos obtener una propiedad concreta, podemos utilizar JSON_EXTRACT().
  • Si queremos acceder a un valor JSON de forma sencilla, podemos utilizar ->.
  • Si queremos obtener el valor sin la representación JSON, podemos utilizar ->>.
  • Si tenemos un array y queremos consultar todos sus elementos como filas, JSON_TABLE() suele ser mucho más apropiado.

Un ejemplo práctico para gráficas

Imaginemos que queremos alimentar una gráfica desde PHP, JavaScript o cualquier otra aplicación.

Nuestro JSON podría contener:

[
    {"count": 20, "age": 19},
    {"count": 142, "age": 27},
    {"count": 85, "age": 31}
]

Podemos extraer todo el array:

SELECT
    JSON_EXTRACT(ageT, '$[*].age') AS edades
FROM table_demo;

Y obtener algo similar a:

[19, 27, 31]

Ese resultado puede ser muy cómodo para posteriormente enviarlo a nuestro frontend y utilizarlo en una librería de gráficos.

Si, por el contrario, queremos trabajar con cada edad y su correspondiente número de usuarios directamente desde SQL, podemos utilizar JSON_TABLE().

Una última consideración: JSON no siempre es la mejor opción

Que MySQL permita almacenar y consultar JSON no significa que debamos meter absolutamente todo dentro de una columna JSON.

Si tenemos datos que vamos a consultar constantemente, filtrar, ordenar, agrupar o relacionar con otras tablas, puede ser mucho más apropiado utilizar una estructura relacional tradicional.

Por ejemplo, si vamos a realizar continuamente consultas del tipo:

SELECT *
FROM usuarios
WHERE edad BETWEEN 18 AND 30;

probablemente tenga más sentido disponer de una columna edad normal, correctamente tipada e indexada, que esconder esa información dentro de un documento JSON.

JSON resulta especialmente interesante cuando necesitamos almacenar estructuras variables o semiestructuradas, pero debemos utilizarlo con criterio.

Conclusión

Trabajar con JSON directamente desde MySQL nos permite evitar tener que recuperar siempre todo el documento y procesarlo posteriormente desde nuestra aplicación.

Con unas pocas herramientas podemos cubrir prácticamente todas las necesidades habituales:

  • JSON_EXTRACT() para extraer información.
  • -> como operador abreviado para acceder a valores JSON.
  • ->> para obtener el valor sin las comillas JSON.
  • $[*] para recorrer todos los elementos de un array.
  • $[0], $[1], etc., para acceder a posiciones concretas.
  • JSON_TABLE() para convertir estructuras JSON en filas que podamos consultar mediante SQL.

Así que sí: si tenemos datos almacenados en JSON, no estamos obligados a sacarlos de MySQL “a pelo” y hacer todo el trabajo desde PHP o JavaScript. MySQL también sabe hablar JSON.

Y bastante bien, además.

Visitas: 677

Website |  + posts

Automation & Data Specialist | Web Development | Data Processing & Integration

Deja un comentario

Privacy Policy