JSON en MySQL: mucho más que guardar un VARCHAR

Cuando apareció el tipo JSON en MySQL, muchos empezamos a usarlo simplemente para guardar documentos. Y, en muchos proyectos, sigue siendo eso: una columna donde se almacena un JSON para luego parsearlo desde Java, C# o cualquier otro lenguaje.

Pero desde MySQL 8, el soporte para JSON es muchísimo más potente.

La base de datos no solo puede almacenar documentos; también puede consultarlos, transformarlos, agregarlos e incluso convertirlos en tablas relacionales.

Por ejemplo, crear un objeto JSON es tan simple como:

SELECT JSON_OBJECT(

    'id', id,

    'name', name,

    'age', age

)

FROM customer;

Y también podemos generar un arreglo JSON a partir de varias filas:

SELECT JSON_ARRAYAGG(name)

FROM customer;

Estas son funciones relativamente conocidas, pero apenas rascan la superficie.

Una de las características más interesantes de MySQL 8 es JSON_TABLE(), que permite tratar un documento JSON como si fuera una tabla relacional y consultarlo con SQL.

Eso significa que, en muchos casos, ya no hace falta traer el documento completo a la aplicación para procesarlo.

La mayoría de las funciones JSON de MySQL permiten leer o modificar un documento. JSON_TABLE() va un paso más allá: convierte un documento JSON en una tabla relacional que puede consultarse con SQL.

Supongamos que tenemos el siguiente JSON:

[

  { "name": "John", "age": 35 },

  { "name": "Mary", "age": 29 },

  { "name": "Alice", "age": 41 }

]

Con JSON_TABLE() podemos recorrer ese documento como si fuera una tabla:

SELECT *

FROM JSON_TABLE(

    '[

      {"name":"John","age":35},

      {"name":"Mary","age":29},

      {"name":"Alice","age":41}

    ]',

    '$[*]'

    COLUMNS (

        name VARCHAR(50) PATH '$.name',

        age INT PATH '$.age'

    )

) AS people;

El resultado es exactamente el que esperaríamos de una consulta tradicional:

| name  | age |

| ----- | --: |

| John  |  35 |

| Mary  |  29 |

| Alice |  41 |

Y ahí está la magia.

Una vez convertido en una tabla, podemos usar WHERE, ORDER BY, GROUP BY, JOIN o cualquier otra característica de SQL.

Por ejemplo:

SELECT name

FROM JSON_TABLE(

    customer.profile,

    '$[*]'

    COLUMNS (

        name VARCHAR(50) PATH '$.name',

        age INT PATH '$.age'

    )

) p

WHERE age >= 30;

Hasta hace algunos años, este trabajo normalmente implicaba traer el JSON completo a la aplicación, deserializarlo y recorrerlo con Java, C# o JavaScript.

Hoy, MySQL puede hacerlo directamente.

Y eso cambia bastante la forma de pensar cuándo conviene procesar un documento en la aplicación... y cuándo conviene dejar que la base de datos haga el trabajo.