30 mins avec les fonctions JSON de MySQL

juillet 23, 2018
Tags: , ,

Read this post in English

Note: L’article suivant peut t’intéresser également: 30 mins avec JSON en MySQL.

 

Comme tu le sais, JSON (JavaScript Object Notation) est un populaire format d’échange de données. Depuis la version 5.7, MySQL supporte un type de données JSON natif (au format interne binaire pour des raisons d’efficacités), ainsi qu’un riche ensemble de fonctions qui te permettront de manipuler dans tout les sens tes documents JSON.

Soyons clair! Cet article n’est pas une revue exhaustive des différentes fonctions JSON implémentées dans MySQL (RTFM! 😉), mais plutôt une sélection arbitraire de certaines d’entre elles.

Note: Depuis MySQL 8 il est désormais possible de manipuler les documents JSON sans SQL (NoSQL) avec MySQL as a document store. (Cette fonctionnalités n’est pas couverte dans cet article).

 

Les exemples ci dessous sont réalisés sans trucage avec MySQL 8.0.11, téléchargeable ici.

MySQL native JSON data type

JSON – Fonctions utilitaires

 

JSON_PRETTY

Améliorer la lisibilité avec JSON_PRETTY

Par défaut, l’affichage d’un document JSON dans MySQL ressemble à ceci :

Tu peux avoir un affichage plus agréable avec JSON_PRETTY :

 

JSON_STORAGE_SIZE

Renvoie le nombre d’octets utilisés pour stocker la représentation binaire d’un document JSON avec JSON_STORAGE_SIZE.

Dans cette collection, le document le plus lourd fait 916 octets, le plus léger 255 et la taille moyenne de tout les documents est 537,2814.

Note: C’est l’espace utilisé pour stocker le document JSON tel qu’il a été inséré dans la colonne, avant toute mise à jour partielle qui aurait pu être effectuée par la suite.

 

Fonctions qui recherchent des valeurs JSON

 

JSON_EXTRACT (->) / JSON_UNQUOTE / ->> operator

JSON_EXTRACT (or ->) retourne des données d’un document JSON.

JSON_UNQUOTE supprime les guillemets des données JSON et renvoie le résultat sous la forme d’une chaîne de caractères utf8mb4.

->> l’opérateur JSON « unquote extract » qui est un raccourci pour JSON_UNQUOTE(JSON_EXTRACT())

 

Les deux requêtes ci-dessus sont similaires.

Pour avoir le même résultat sans les guillemets utilise ->> ou JSON_UNQUOTE(JSON_EXTRACT()) :

Les deux requêtes ci-dessus sont similaires.

 

JSON_CONTAINS

Recherche si la valeur de la clé correspond à une valeur spécifiée avec JSON_CONTAINS.

 

 

JSON_CONTAINS_PATH

Indique si un document JSON contient des données dans l’un ou les chemins spécifiés avec JSON_CONTAINS_PATH.

 

Pour tester cette fonction, j’insère un document factice dans la collection restaurants :

Combien y a t’il de documents sans note (grades) ?

Un seul !  Tu peux alors facilement vérifier la structure de ce document :

 

Un pont entre ces deux modèles

 

Pour paraphraser David Stokes (MySQL Community Manager) dans son livre MySQL and JSON – A practical Programming Guide.

« 

The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless-data needs to be in a schema. 

« 

Faire de tels transformations avec MySQL est extrêmement aisé !

 

Relationnel vers JSON

JSON_OBJECT

Évalue une liste de paires clé/valeur et renvoie un objet JSON contenant ces paires avec JSON_OBJECT.

Une requête SQL traditionnelle avec un jeu de résultats relationnel. En d’autres termes, le document JSON génère des données non-JSON :

Ce jeu de résultats peut être converti au format JSON, plus précisément en un objet JSON :

Autre exemple :

 

 

JSON_OBJECTAGG

Prend deux noms de colonnes ou expressions et renvoie un objet JSON contenant des paires clé/valeur avec JSON_OBJECTAGG.

Agréger des colonnes est très utile en SQL.

  • Note
    • De manière générale, c’est plutôt une très mauvaise idée d’utiliser ORDER BY RAND() pour générer des enregistrements aléatoires, car ce n’est pas scalable (en clair, problèmes de performance avec de grosses tables).
    • Il vaut mieux gérer l’aléatoire au niveau de l’application ou alors pré-calculer les valeurs aléatoires et les stocker dans la base.

 

 

JSON_ARRAY

Evalue une liste de valeurs et retourne un tableau JSON contenant ces valeurs avec JSON_ARRAY.

L’exemple qui suit est une requête Common Table Expression récursive aka recursive CTE (ou encore requête WITH) qui permet de parcourir une hiérarchie sans connaitre sa profondeur :

Générer du JSON avec JSON_OBJECT & JSON_ARRAY :

 

 

JSON_ARRAYAGG

Agréger un ensemble de résultats en un seul tableau JSON dont les éléments sont constitués des lignes avec JSON_ARRAYAGG.

A l’aide de cette autre fonction JSON d’agrégation voici différentes requêtes SQL qui génèrent du JSON :

 

 

 

 

JSON vers Relationnel

Maintenant le processus inverse. Transformation des données JSON en données relationnelles.

 

JSON_TABLE

Extrait les données d’un document JSON et renvoies-les en tant que table relationnelle avec JSON_TABLE.

Conseil amical, je te recommande fortement de passer du temps dans la documentation de cette puissante et complète fonction, qui va te permettre de mapper des données JSON dans une table relationnelle temporaire, puis d’interroger cette dernière.

Assez de blabla, voici quelques exemples :

 

 

 

JSON_TABLE – Nested Data

Parcours le chemin du document JSON et récupère les données imbriquées.

Par exemple, extraire toutes les notes (grades) des restaurants qui font de la cuisine Hawaiian :

 

JSON_TABLE – Missing Data

Précise quelle action à accomplir en cas de données manquantes.

Comportement par défaut :

 

Renforce le comportement par défaut :

 

Déclenche une erreur :

 

Mettre une valeur par défaut :

 

 

Le(s) mot(s) de la fin

MySQL 8 et 5.7 possèdent un riche jeu de fonctions JSON. J’en ai présenté quelques unes mais rassures toi il t’en reste encore pas mal à découvrir, notamment pour créer, modifier,indexer… les documents.

A noter également que si le modèle relationnel ne convient pas à ton workload, MySQL 8 Document Store t’offre la possibilité de gérer tes collections à l’aide d’une API CRUD NoSQL. J’en parlerai plus en détail dans un prochain article.

Pour patienter je t’invite à lire : Top 10 reasons for NoSQL with MySQL.

 

 

Pour aller plus loin

Documentation

 

Articles

 

Autres ressources

  • Tu trouveras les bases de données utilisées dans cet article ici.
  • Le dump de la collection Restaurants ici.
  • Quelques livres qui peuvent être utile : ici.

 

 

Thanks for using MySQL!

 

2 Responses to “30 mins avec les fonctions JSON de MySQL”

  1. […] Lire cet article en français […]

  2. […] Note 2: L’article suivant peut t’intéresser également: 30 mins avec les fonctions JSON de MySQL. […]

Leave a Reply