30 mins avec les fonctions JSON de MySQL

juillet 23, 2018

Tags: CTE, JSON, NoSQL

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

Note 2: Tu peux également manipuler tes documents JSON avec MySQL Document Store.

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.

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 :

SELECT doc FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
doc: {"_id": "564b3259666906a86ea90a99", "name": "Dj Reynolds Pub And Restaurant", "grades": [{"date": {"$date": 1409961600000}, "grade": "A", "score": 2}, {"date": {"$date": 1374451200000}, "grade": "A", "score": 11}, {"date": {"$date": 1343692800000}, "grade": "A", "score": 12}, {"date": {"$date": 1325116800000}, "grade": "A", "score": 12}], "address": {"coord": [-73.98513559999999, 40.7676919], "street": "West   57 Street", "zipcode": "10019", "building": "351"}, "borough": "Manhattan", "cuisine": "Irish", "restaurant_id": "30191841"}

SELECT doc FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

doc: {"_id": "564b3259666906a86ea90a99", "name": "Dj Reynolds Pub And Restaurant", "grades": [{"date": {"$date": 1409961600000}, "grade": "A", "score": 2}, {"date": {"$date": 1374451200000}, "grade": "A", "score": 11}, {"date": {"$date": 1343692800000}, "grade": "A", "score": 12}, {"date": {"$date": 1325116800000}, "grade": "A", "score": 12}], "address": {"coord": [-73.98513559999999, 40.7676919], "street": "West 57 Street", "zipcode": "10019", "building": "351"}, "borough": "Manhattan", "cuisine": "Irish", "restaurant_id": "30191841"}

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

SELECT JSON_PRETTY(doc) FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_PRETTY(doc): {
  "_id": "564b3259666906a86ea90a99",
  "name": "Dj Reynolds Pub And Restaurant",
  "grades": [
    {
      "date": {
        "$date": 1409961600000
      },
      "grade": "A",
      "score": 2
    },
    {
      "date": {
        "$date": 1374451200000
      },
      "grade": "A",
      "score": 11
    },
    {
      "date": {
        "$date": 1343692800000
      },
      "grade": "A",
      "score": 12
    },
    {
      "date": {
        "$date": 1325116800000
      },
      "grade": "A",
      "score": 12
    }
  ],
  "address": {
    "coord": [
      -73.98513559999999,
      40.7676919
    ],
    "street": "West   57 Street",
    "zipcode": "10019",
    "building": "351"
  },
  "borough": "Manhattan",
  "cuisine": "Irish",
  "restaurant_id": "30191841"
}

SELECT JSON_PRETTY(doc) FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_PRETTY(doc): {

"_id": "564b3259666906a86ea90a99",

"name": "Dj Reynolds Pub And Restaurant",

"grades": [

{

"date": {

"$date": 1409961600000

"grade": "A",

"score": 2

{

"date": {

"$date": 1374451200000

"grade": "A",

"score": 11

{

"date": {

"$date": 1343692800000

"grade": "A",

"score": 12

{

"date": {

"$date": 1325116800000

"grade": "A",

"score": 12

}

"address": {

"coord": [

-73.98513559999999,

40.7676919

"street": "West 57 Street",

"zipcode": "10019",

"building": "351"

"borough": "Manhattan",

"cuisine": "Irish",

"restaurant_id": "30191841"

}

JSON_STORAGE_SIZE

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

SELECT max(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| max(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                         916 |
+-----------------------------+

SELECT avg(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| avg(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                    537.2814 |
+-----------------------------+

SELECT min(JSON_STORAGE_SIZE(doc)) FROM restaurants;
+-----------------------------+
| min(JSON_STORAGE_SIZE(doc)) |
+-----------------------------+
|                         255 |
+-----------------------------+

SELECT max(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| max(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 916 |

+-----------------------------+

SELECT avg(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| avg(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 537.2814 |

+-----------------------------+

SELECT min(JSON_STORAGE_SIZE(doc)) FROM restaurants;

+-----------------------------+

| min(JSON_STORAGE_SIZE(doc)) |

+-----------------------------+

| 255 |

+-----------------------------+

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())

SELECT JSON_EXTRACT(doc, "$.cuisine") FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_EXTRACT(doc, "$.cuisine"): "Irish"


SELECT doc->"$.cuisine" FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
doc->"$.cuisine": "Irish"

SELECT JSON_EXTRACT(doc, "$.cuisine") FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_EXTRACT(doc, "$.cuisine"): "Irish"

SELECT doc->"$.cuisine" FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

doc->"$.cuisine": "Irish"

Les deux requêtes ci-dessus sont similaires.

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

SELECT JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")) FROM restaurants LIMIT 1\G
*************************** 1. row ***************************
JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")): Irish


SELECT doc->>"$.cuisine" FROM restaurants LIMIT 1\G
doc->>"$.cuisine": Irish

SELECT JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")) FROM restaurants LIMIT 1\G

*************************** 1. row ***************************

JSON_UNQUOTE(JSON_EXTRACT(doc, "$.cuisine")): Irish

SELECT doc->>"$.cuisine" FROM restaurants LIMIT 1\G

doc->>"$.cuisine": Irish

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.

SELECT count(*) 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');
+----------+
| count(*) |
+----------+
|       24 |
+----------+


SELECT doc->>"$.name" 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');
+-----------------------------------------------+
| doc->>"$.name"                                |
+-----------------------------------------------+
| Belvedere Restaurant                          |
| Chez Macoule Restaurant                       |
| Paradise Venus Restaurant                     |
| Heavenly Fritaille Restaurant                 |
| Yolie'S Bar & Restaurant                      |
| Yo-Yo Fritaille                               |
| Kal Bakery & Restaurant                       |
| Bon Appetit Restaurant                        |
| Katou Fin Restaurant                          |
| Alhpa Restaurant                              |
| Lakay Buffet Restaurant                       |
| La Tranquilite Restaurant                     |
| La Caye Restaurant                            |
| Nous Les Amis Restaurant & Bakery             |
| Yoyo Fritaille                                |
| Fresh Crown Restaurant                        |
| Tonel Restaurant & Lounge                     |
| Grace Devine Pastry And Restaurant Restaurant |
| Viva Bubble Tea                               |
| Cafe Creole Restaurant N Bakery               |
| Delly'S Place Restaurant & Fritaille          |
| Creole Plate                                  |
| Chez Nous Restaurant & Fritaille              |
| Combite Creole                                |
+-----------------------------------------------+

SELECT count(*)

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');

+----------+

| count(*) |

+----------+

| 24 |

+----------+

SELECT doc->>"$.name"

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine');

+-----------------------------------------------+

| doc->>"$.name" |

+-----------------------------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

| Paradise Venus Restaurant |

| Heavenly Fritaille Restaurant |

| Yolie'S Bar & Restaurant |

| Yo-Yo Fritaille |

| Kal Bakery & Restaurant |

| Bon Appetit Restaurant |

| Katou Fin Restaurant |

| Alhpa Restaurant |

| Lakay Buffet Restaurant |

| La Tranquilite Restaurant |

| La Caye Restaurant |

| Nous Les Amis Restaurant & Bakery |

| Yoyo Fritaille |

| Fresh Crown Restaurant |

| Tonel Restaurant & Lounge |

| Grace Devine Pastry And Restaurant Restaurant |

| Viva Bubble Tea |

| Cafe Creole Restaurant N Bakery |

| Delly'S Place Restaurant & Fritaille |

| Creole Plate |

| Chez Nous Restaurant & Fritaille |

| Combite Creole |

+-----------------------------------------------+

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 :

INSERT INTO restaurants (doc) VALUES ('{"_id": "1234", "name": "Daz Restaurant", "cuisine": "West Indian", "restaurant_id": "4321"}');

1	INSERT INTO restaurants (doc) VALUES ('{"_id": "1234", "name": "Daz Restaurant", "cuisine": "West Indian", "restaurant_id": "4321"}');

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

SELECT count(*), JSON_CONTAINS_PATH(doc, 'one', '$.grades') cp 
FROM restaurants 
GROUP BY cp;
+----------+------+
| count(*) | cp   |
+----------+------+
|        1 |    0 |
|    25359 |    1 |
+----------+------+

SELECT count(*), JSON_CONTAINS_PATH(doc, 'one', '$.grades') cp

FROM restaurants

GROUP BY cp;

+----------+------+

| count(*) | cp |

+----------+------+

| 1 | 0 |

| 25359 | 1 |

+----------+------+

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

SELECT JSON_PRETTY(doc) 
FROM restaurants 
WHERE JSON_CONTAINS_PATH(doc, 'one', '$.grades') = 0\G
*************************** 1. row ***************************
JSON_PRETTY(doc): {
  "_id": "1234",
  "name": "Daz Restaurant",
  "cuisine": "West Indian",
  "restaurant_id": "4321"
}

SELECT JSON_PRETTY(doc)

FROM restaurants

WHERE JSON_CONTAINS_PATH(doc, 'one', '$.grades') = 0\G

*************************** 1. row ***************************

JSON_PRETTY(doc): {

"_id": "1234",

"name": "Daz Restaurant",

"cuisine": "West Indian",

"restaurant_id": "4321"

}

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 :

SELECT doc->>"$.name" 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine') 
LIMIT 2;
+-------------------------+
| doc->>"$.name"          |
+-------------------------+
| Belvedere Restaurant    |
| Chez Macoule Restaurant |
+-------------------------+

SELECT doc->>"$.name"

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine')

LIMIT 2;

+-------------------------+

| doc->>"$.name" |

+-------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

+-------------------------+

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

SELECT JSON_OBJECT("Name", doc->>"$.name") 
FROM restaurants 
WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine') 
LIMIT 2;
+-------------------------------------+
| JSON_OBJECT("Name", doc->>"$.name") |
+-------------------------------------+
| {"Name": "Belvedere Restaurant"}    |
| {"Name": "Chez Macoule Restaurant"} |
+-------------------------------------+

SELECT JSON_OBJECT("Name", doc->>"$.name")

FROM restaurants

WHERE JSON_CONTAINS(doc, '"Creole"', '$.cuisine')

LIMIT 2;

+-------------------------------------+

| JSON_OBJECT("Name", doc->>"$.name") |

+-------------------------------------+

| {"Name": "Belvedere Restaurant"} |

| {"Name": "Chez Macoule Restaurant"} |

+-------------------------------------+

Autre exemple :

SELECT Name, Population 
FROM City 
WHERE CountryCode='fra' 
ORDER BY Population DESC 
LIMIT 5;
+-----------+------------+
| Name      | Population |
+-----------+------------+
| Paris     |    2125246 |
| Marseille |     798430 |
| Lyon      |     445452 |
| Toulouse  |     390350 |
| Nice      |     342738 |
+-----------+------------+


SELECT JSON_OBJECT("CityName",Name, "CityPop", Population) 
FROM City 
WHERE CountryCode='fra' 
ORDER BY Population DESC 
LIMIT 5;
+-----------------------------------------------------+
| JSON_OBJECT("CityName",Name, "CityPop", Population) |
+-----------------------------------------------------+
| {"CityPop": 2125246, "CityName": "Paris"}           |
| {"CityPop": 798430, "CityName": "Marseille"}        |
| {"CityPop": 445452, "CityName": "Lyon"}             |
| {"CityPop": 390350, "CityName": "Toulouse"}         |
| {"CityPop": 342738, "CityName": "Nice"}             |
+-----------------------------------------------------+

SELECT Name, Population

FROM City

WHERE CountryCode='fra'

ORDER BY Population DESC

LIMIT 5;

+-----------+------------+

| Name | Population |

+-----------+------------+

| Paris | 2125246 |

| Marseille | 798430 |

| Lyon | 445452 |

| Toulouse | 390350 |

| Nice | 342738 |

+-----------+------------+

SELECT JSON_OBJECT("CityName",Name, "CityPop", Population)

FROM City

WHERE CountryCode='fra'

ORDER BY Population DESC

LIMIT 5;

+-----------------------------------------------------+

| JSON_OBJECT("CityName",Name, "CityPop", Population) |

+-----------------------------------------------------+

| {"CityPop": 2125246, "CityName": "Paris"} |

| {"CityPop": 798430, "CityName": "Marseille"} |

| {"CityPop": 445452, "CityName": "Lyon"} |

| {"CityPop": 390350, "CityName": "Toulouse"} |

| {"CityPop": 342738, "CityName": "Nice"} |

+-----------------------------------------------------+

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.

SELECT JSON_OBJECTAGG(Name, CountryCode) 
FROM City  
GROUP BY id 
ORDER BY RAND() 
LIMIT 5;
+-----------------------------------+
| JSON_OBJECTAGG(Name, CountryCode) |
+-----------------------------------+
| {"Reno": "USA"}                   |
| {"Hanam": "KOR"}                  |
| {"Laizhou": "CHN"}                |
| {"Yogyakarta": "IDN"}             |
| {"Tantoyuca": "MEX"}              |
+-----------------------------------+

SELECT JSON_OBJECTAGG(Name, CountryCode)

FROM City

GROUP BY id

ORDER BY RAND()

LIMIT 5;

+-----------------------------------+

| JSON_OBJECTAGG(Name, CountryCode) |

+-----------------------------------+

| {"Reno": "USA"} |

| {"Hanam": "KOR"} |

| {"Laizhou": "CHN"} |

| {"Yogyakarta": "IDN"} |

| {"Tantoyuca": "MEX"} |

+-----------------------------------+

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 :

WITH RECURSIVE emp_ext (id, name, path) AS ( 
    SELECT id, name, CAST(id AS CHAR(200)) 
    FROM employees 
    WHERE manager_id IS NULL 
    UNION ALL 
    SELECT s.id, s.name, CONCAT(m.path, ",", s.id) 
    FROM emp_ext m 
        JOIN employees s ON m.id=s.manager_id 
) 
SELECT id,name, path FROM emp_ext ORDER BY path;
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+

WITH RECURSIVE emp_ext (id, name, path) AS (

SELECT id, name, CAST(id AS CHAR(200))

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT s.id, s.name, CONCAT(m.path, ",", s.id)

FROM emp_ext m

JOIN employees s ON m.id=s.manager_id

)

SELECT id,name, path FROM emp_ext ORDER BY path;

+------+---------+-----------------+

| id | name | path |

+------+---------+-----------------+

| 333 | Yasmina | 333 |

| 198 | John | 333,198 |

| 29 | Pedro | 333,198,29 |

| 4610 | Sarah | 333,198,29,4610 |

| 72 | Pierre | 333,198,29,72 |

| 692 | Tarek | 333,692 |

| 123 | Adil | 333,692,123 |

+------+---------+-----------------+

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

WITH RECURSIVE emp_ext (id, name, path) AS ( 
    SELECT id, name, CAST(id AS CHAR(200)) 
    FROM employees 
    WHERE manager_id IS NULL 
    UNION ALL 
    SELECT s.id, s.name, CONCAT(m.path, ",", s.id) 
    FROM emp_ext m 
        JOIN employees s ON m.id=s.manager_id 
) 
SELECT JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) 
FROM emp_ext 
ORDER BY path;
+-------------------------------------------------------------+
| JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) |
+-------------------------------------------------------------+
| {"ID": 333, "Name": "Yasmina", "Path": ["333"]}             |
| {"ID": 198, "Name": "John", "Path": ["333,198"]}            |
| {"ID": 29, "Name": "Pedro", "Path": ["333,198,29"]}         |
| {"ID": 4610, "Name": "Sarah", "Path": ["333,198,29,4610"]}  |
| {"ID": 72, "Name": "Pierre", "Path": ["333,198,29,72"]}     |
| {"ID": 692, "Name": "Tarek", "Path": ["333,692"]}           |
| {"ID": 123, "Name": "Adil", "Path": ["333,692,123"]}        |
+-------------------------------------------------------------+

WITH RECURSIVE emp_ext (id, name, path) AS (

SELECT id, name, CAST(id AS CHAR(200))

FROM employees

WHERE manager_id IS NULL

UNION ALL

SELECT s.id, s.name, CONCAT(m.path, ",", s.id)

FROM emp_ext m

JOIN employees s ON m.id=s.manager_id

)

SELECT JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path))

FROM emp_ext

ORDER BY path;

+-------------------------------------------------------------+

| JSON_OBJECT("ID",id, "Name",name, "Path", JSON_ARRAY(path)) |

+-------------------------------------------------------------+

| {"ID": 333, "Name": "Yasmina", "Path": ["333"]} |

| {"ID": 198, "Name": "John", "Path": ["333,198"]} |

| {"ID": 29, "Name": "Pedro", "Path": ["333,198,29"]} |

| {"ID": 4610, "Name": "Sarah", "Path": ["333,198,29,4610"]} |

| {"ID": 72, "Name": "Pierre", "Path": ["333,198,29,72"]} |

| {"ID": 692, "Name": "Tarek", "Path": ["333,692"]} |

| {"ID": 123, "Name": "Adil", "Path": ["333,692,123"]} |

+-------------------------------------------------------------+

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 :

SELECT CountryCode, JSON_ARRAYAGG(City.Name) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY 1 
LIMIT 5;
+-------------+--------------------------------------------------------------------------------------------------------------+
| CountryCode | JSON_ARRAYAGG(City.Name)                                                                                     |
+-------------+--------------------------------------------------------------------------------------------------------------+
| ALB         | ["Tirana"]                                                                                                   |
| AND         | ["Andorra la Vella"]                                                                                         |
| AUT         | ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]                                              |
| BEL         | ["Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons", "Bruxelles [Brussel]"]  |
| BGR         | ["Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric", "Ruse"]      |
+-------------+--------------------------------------------------------------------------------------------------------------+

SELECT CountryCode, JSON_ARRAYAGG(City.Name)

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY 1

LIMIT 5;

+-------------+--------------------------------------------------------------------------------------------------------------+

| CountryCode | JSON_ARRAYAGG(City.Name) |

+-------------+--------------------------------------------------------------------------------------------------------------+

| ALB | ["Tirana"] |

| AND | ["Andorra la Vella"] |

| AUT | ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"] |

| BEL | ["Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons", "Bruxelles [Brussel]"] |

| BGR | ["Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric", "Ruse"] |

+-------------+--------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("CountryCode",CountryCode), JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name)) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY 1 
LIMIT 5;
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECT("CountryCode",CountryCode) | JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name))                                                                           |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+
| {"CountryCode": "ALB"}                 | {"CityName": ["Tirana"]}                                                                                                   |
| {"CountryCode": "AND"}                 | {"CityName": ["Andorra la Vella"]}                                                                                         |
| {"CountryCode": "AUT"}                 | {"CityName": ["Wien", "Graz", "Linz", "Salzburg", "Innsbruck", "Klagenfurt"]}                                              |
| {"CountryCode": "BEL"}                 | {"CityName": ["Schaerbeek", "Mons", "Namur", "Brugge", "Liège", "Antwerpen", "Charleroi", "Gent", "Bruxelles [Brussel]"]}  |
| {"CountryCode": "BGR"}                 | {"CityName": ["Burgas", "Šumen", "Dobric", "Sliven", "Pleven", "Stara Zagora", "Ruse", "Varna", "Plovdiv", "Sofija"]}      |
+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("CountryCode",CountryCode), JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name))

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY 1

LIMIT 5;

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

| JSON_OBJECT("CountryCode",CountryCode) | JSON_OBJECT("CityName",JSON_ARRAYAGG(City.Name)) |

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

| {"CountryCode": "ALB"} | {"CityName": ["Tirana"]} |

| {"CountryCode": "AND"} | {"CityName": ["Andorra la Vella"]} |

| {"CountryCode": "AUT"} | {"CityName": ["Wien", "Graz", "Linz", "Salzburg", "Innsbruck", "Klagenfurt"]} |

| {"CountryCode": "BEL"} | {"CityName": ["Schaerbeek", "Mons", "Namur", "Brugge", "Liège", "Antwerpen", "Charleroi", "Gent", "Bruxelles [Brussel]"]} |

| {"CountryCode": "BGR"} | {"CityName": ["Burgas", "Šumen", "Dobric", "Sliven", "Pleven", "Stara Zagora", "Ruse", "Varna", "Plovdiv", "Sofija"]} |

+----------------------------------------+----------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name)) 
FROM City 
    JOIN Country ON (City.CountryCode=Country.Code) 
WHERE Continent='Europe' 
GROUP BY CountryCode 
LIMIT 5;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name))                                                                     |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| {"Code": "ALB", "CityName": ["Tirana"]}                                                                                                   |
| {"Code": "AND", "CityName": ["Andorra la Vella"]}                                                                                         |
| {"Code": "AUT", "CityName": ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]}                                              |
| {"Code": "BEL", "CityName": ["Bruxelles [Brussel]", "Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons"]}  |
| {"Code": "BGR", "CityName": ["Ruse", "Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric"]}      |
+-------------------------------------------------------------------------------------------------------------------------------------------+

SELECT JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name))

FROM City

JOIN Country ON (City.CountryCode=Country.Code)

WHERE Continent='Europe'

GROUP BY CountryCode

LIMIT 5;

+-------------------------------------------------------------------------------------------------------------------------------------------+

| JSON_OBJECT("Code",CountryCode, "CityName", JSON_ARRAYAGG(City.Name)) |

+-------------------------------------------------------------------------------------------------------------------------------------------+

| {"Code": "ALB", "CityName": ["Tirana"]} |

| {"Code": "AND", "CityName": ["Andorra la Vella"]} |

| {"Code": "AUT", "CityName": ["Graz", "Linz", "Salzburg", "Innsbruck", "Wien", "Klagenfurt"]} |

| {"Code": "BEL", "CityName": ["Bruxelles [Brussel]", "Antwerpen", "Brugge", "Gent", "Schaerbeek", "Charleroi", "Namur", "Liège", "Mons"]} |

| {"Code": "BGR", "CityName": ["Ruse", "Šumen", "Sofija", "Stara Zagora", "Plovdiv", "Pleven", "Varna", "Sliven", "Burgas", "Dobric"]} |

+-------------------------------------------------------------------------------------------------------------------------------------------+

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 :

SELECT GNP 
FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP")) AS jst 
WHERE _id='FRA';
+---------+
| GNP     |
+---------+
| 1424285 |
+---------+

SELECT GNP

FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP")) AS jst

WHERE _id='FRA';

+---------+

| GNP |

+---------+

| 1424285 |

+---------+

SELECT GNP, Name, LifeExpectancy 
FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP", Name char(255) PATH "$.Name", LifeExpectancy int PATH "$.demographics.LifeExpectancy")) AS jst 
WHERE _id IN ('FRA', 'USA');
+---------+---------------+----------------+
| GNP     | Name          | LifeExpectancy |
+---------+---------------+----------------+
| 1424285 | France        |             79 |
| 8510700 | United States |             77 |
+---------+---------------+----------------+

SELECT GNP, Name, LifeExpectancy

FROM countryinfo, JSON_TABLE(doc, "$" COLUMNS (GNP int PATH "$.GNP", Name char(255) PATH "$.Name", LifeExpectancy int PATH "$.demographics.LifeExpectancy")) AS jst

WHERE _id IN ('FRA', 'USA');

+---------+---------------+----------------+

| GNP | Name | LifeExpectancy |

+---------+---------------+----------------+

| 1424285 | France | 79 |

| 8510700 | United States | 77 |

+---------+---------------+----------------+

SELECT name AS "Creole Cuisine" 
FROM restaurant.restaurants, JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine")) AS jst 
WHERE cuisine='Creole';
+-----------------------------------------------+
| Creole Cuisine                                |
+-----------------------------------------------+
| Belvedere Restaurant                          |
| Chez Macoule Restaurant                       |
| Paradise Venus Restaurant                     |
| Heavenly Fritaille Restaurant                 |
| Yolie'S Bar & Restaurant                      |
| Yo-Yo Fritaille                               |
| Kal Bakery & Restaurant                       |
| Bon Appetit Restaurant                        |
| Katou Fin Restaurant                          |
| Alhpa Restaurant                              |
| Lakay Buffet Restaurant                       |
| La Tranquilite Restaurant                     |
| La Caye Restaurant                            |
| Nous Les Amis Restaurant & Bakery             |
| Yoyo Fritaille                                |
| Fresh Crown Restaurant                        |
| Tonel Restaurant & Lounge                     |
| Grace Devine Pastry And Restaurant Restaurant |
| Viva Bubble Tea                               |
| Cafe Creole Restaurant N Bakery               |
| Delly'S Place Restaurant & Fritaille          |
| Creole Plate                                  |
| Chez Nous Restaurant & Fritaille              |
| Combite Creole                                |
+-----------------------------------------------+

SELECT name AS "Creole Cuisine"

FROM restaurant.restaurants, JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine")) AS jst

WHERE cuisine='Creole';

+-----------------------------------------------+

| Creole Cuisine |

+-----------------------------------------------+

| Belvedere Restaurant |

| Chez Macoule Restaurant |

| Paradise Venus Restaurant |

| Heavenly Fritaille Restaurant |

| Yolie'S Bar & Restaurant |

| Yo-Yo Fritaille |

| Kal Bakery & Restaurant |

| Bon Appetit Restaurant |

| Katou Fin Restaurant |

| Alhpa Restaurant |

| Lakay Buffet Restaurant |

| La Tranquilite Restaurant |

| La Caye Restaurant |

| Nous Les Amis Restaurant & Bakery |

| Yoyo Fritaille |

| Fresh Crown Restaurant |

| Tonel Restaurant & Lounge |

| Grace Devine Pastry And Restaurant Restaurant |

| Viva Bubble Tea |

| Cafe Creole Restaurant N Bakery |

| Delly'S Place Restaurant & Fritaille |

| Creole Plate |

| Chez Nous Restaurant & Fritaille |

| Combite Creole |

+-----------------------------------------------+

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 :

SELECT name, cuisine, gradeID, grade 
FROM restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", NESTED PATH "$.grades[*]" COLUMNS (gradeID FOR ORDINALITY, grade char(20) PATH "$.grade"))) AS jst 
WHERE cuisine='Hawaiian';
+------------------+----------+---------+-------+
| name             | cuisine  | gradeID | grade |
+------------------+----------+---------+-------+
| Makana           | Hawaiian |       1 | C     |
| Makana           | Hawaiian |       2 | C     |
| Makana           | Hawaiian |       3 | A     |
| Makana           | Hawaiian |       4 | C     |
| Makana           | Hawaiian |       5 | A     |
| General Assembly | Hawaiian |       1 | A     |
| General Assembly | Hawaiian |       2 | A     |
| General Assembly | Hawaiian |       3 | A     |
| General Assembly | Hawaiian |       4 | A     |
| Onomea           | Hawaiian |       1 | A     |
| Onomea           | Hawaiian |       2 | A     |
+------------------+----------+---------+-------+

SELECT name, cuisine, gradeID, grade

FROM restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", NESTED PATH "$.grades[*]" COLUMNS (gradeID FOR ORDINALITY, grade char(20) PATH "$.grade"))) AS jst

WHERE cuisine='Hawaiian';

+------------------+----------+---------+-------+

+------------------+----------+---------+-------+

| Makana | Hawaiian | 1 | C |

| Makana | Hawaiian | 2 | C |

| Makana | Hawaiian | 3 | A |

| Makana | Hawaiian | 4 | C |

| Makana | Hawaiian | 5 | A |

| General Assembly | Hawaiian | 1 | A |

| General Assembly | Hawaiian | 2 | A |

| General Assembly | Hawaiian | 3 | A |

| General Assembly | Hawaiian | 4 | A |

| Onomea | Hawaiian | 1 | A |

| Onomea | Hawaiian | 2 | A |

+------------------+----------+---------+-------+

JSON_TABLE – Missing Data

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

Comportement par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough")) AS jst  
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | NULL      |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough")) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | NULL |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

Renforce le comportement par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" NULL ON EMPTY)) AS jst 
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | NULL      |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" NULL ON EMPTY)) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | NULL |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

Déclenche une erreur :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" ERROR ON EMPTY)) AS jst 
LIMIT 2;
ERROR 3665 (22035): Missing value for JSON_TABLE column 'borough'

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" ERROR ON EMPTY)) AS jst

LIMIT 2;

ERROR 3665 (22035): Missing value for JSON_TABLE column 'borough'

Mettre une valeur par défaut :

SELECT name, cuisine, borough 
FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" DEFAULT '"<UNKNOW>"' ON EMPTY)) AS jst 
LIMIT 2;
+--------------------------------+-------------+-----------+
| name                           | cuisine     | borough   |
+--------------------------------+-------------+-----------+
| Daz Restaurant                 | West Indian | <UNKNOW>  |
| Dj Reynolds Pub And Restaurant | Irish       | Manhattan |
+--------------------------------+-------------+-----------+

SELECT name, cuisine, borough

FROM restaurant.restaurants,JSON_TABLE(doc, "$" COLUMNS (name char(100) PATH "$.name", cuisine char(100) PATH "$.cuisine", borough char(100) PATH "$.borough" DEFAULT '"<UNKNOW>"' ON EMPTY)) AS jst

LIMIT 2;

+--------------------------------+-------------+-----------+

| name | cuisine | borough |

+--------------------------------+-------------+-----------+

| Daz Restaurant | West Indian | <UNKNOW> |

| Dj Reynolds Pub And Restaurant | Irish | Manhattan |

+--------------------------------+-------------+-----------+

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!

Olivier DASINI

MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

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

30 mins with MySQL JSON functions | dasini.net - Journal d'un expert MySQL

juillet 24th, 2018 at 9:32

[…] Lire cet article en français […]
30 mins avec JSON en MySQL | dasini.net - Journal d'un expert MySQL

août 6th, 2018 at 14:53

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

dasini.net – Journal d'un expert MySQL