Manipulation des colonnes JSON avec MySQL 8 Part 2: objet simples

Suite au premier article montrant les possibilités des fonctions JSON de MySQL8 sur un champ JSON contenant un tableau simple, nous allons voir ce qui est possible de faire avec un objet JSON.

Cas d'exemple

Cela permet de remplacer un EAV (Entity Attribute Value) qui est très lourd à manipuler, en quelque chose de plus simple. Pour ceux qui ne connaissent pas le concept, c'est un modèle de données pour sauvegarder un grand nombre d'attributs possibles pour une entité lorsque ceux ci ne sont pas tous renseignés. Habituellement il y a une table pour l'entité, une table pour la liste des attributs possibles et une table de liaison contenant l'identifiant de l'entité, l'identifiant de l'attribut et sa valeur.

Pour ce cas, prenons comme exemple une base de disque, les attributs optionnels, comme le fait que ce soit une version promo, limité ou couleur, sont stockés dans une colonne properties. Cela pourrait donner ceci:

La table:

mysql> SHOW CREATE TABLE disc\G
*************************** 1. row ***************************
       Table: disc
Create Table: CREATE TABLE `disc` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `band` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `support` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `properties` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0,00 sec)
 

Les données

mysql> SELECT * FROM disc;
+----+-------------+-----------------------------+---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------+
| id | band        | title                       | label               | support | country_code | properties                                                                                                                |
+----+-------------+-----------------------------+---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------+
|  1 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | DE           | {"limited": true, "quantity": 1500, "reference": "3984-14639-1", "number_of_disc": 2}                                     |
|  2 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | DE           | {"picture": true, "reference": "3984-14693-7 PD", "test_pressing": true}                                                  |
|  3 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | DE           | {"limited": true, "picture": true, "numbered": true, "quantity": 500, "reference": "3984-14693-7 PD"}                     |
|  4 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | DE           | {"promo": true, "picture": true, "reference": "3984-14693-7 PD"}                                                          |
|  5 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | USA          | {"picture": true, "reference": "3984-14639-1", "test_pressing": true}                                                     |
|  6 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | USA          | {"limited": true, "picture": true, "numbered": true, "quantity": 2000, "reference": "3984-14639-1"}                       |
|  7 | Amon Amarth | Twilight Of The Thunder God | Back On Black       | LP      | UK           | {"reference": "BOBV136LP", "test_pressing": true, "number_of_disc": 2}                                                    |
|  8 | Amon Amarth | Twilight Of The Thunder God | Back On Black       | LP      | UK           | {"color": "clear", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                        |
|  9 | Amon Amarth | Twilight Of The Thunder God | Back On Black       | LP      | UK           | {"color": "blue", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                         |
| 10 | Amon Amarth | Twilight Of The Thunder God | Back On Black       | LP      | UK           | {"color": "white", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                        |
| 11 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "yellow/red", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"} |
| 12 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "orange", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}     |
| 13 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "brown", "limited": true, "reissue": true, "numbered": true, "quantity": 100, "reference": "3984-25050-1"}      |
| 14 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "blue", "limited": true, "reissue": true, "quantity": 500, "reference": "3984-25050-1"}                         |
| 15 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "green", "limited": true, "reissue": true, "numbered": true, "quantity": 300, "reference": "3984-25050-1"}      |
| 16 | Amon Amarth | Twilight Of The Thunder God | Metal Blade records | LP      | EU           | {"color": "clear", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}      |
+----+-------------+-----------------------------+---------------------+---------+--------------+---------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0,00 sec)


Extraction des données

Dans le cas d'un objet JSON, la fonction JSON_LENGTH renvoie le nombre de propriétés dans le JSON, et la fonction JSON_EXTRACT permet de récupérer la valeur d'une propriété spécifique (par exemple la référence ou la couleur) que celle-ci soit présente où nom dans le JSON

mysql> SELECT id, 
JSON_LENGTH(properties) AS properties_count, 
JSON_EXTRACT(properties, '$.reference') AS reference, 
JSON_EXTRACT(properties, '$.color') AS color 
FROM disc;
+----+------------------+-------------------+--------------+
| id | properties_count | reference         | color        |
+----+------------------+-------------------+--------------+
|  1 |                4 | "3984-14639-1"    | NULL         |
|  2 |                3 | "3984-14693-7 PD" | NULL         |
|  3 |                5 | "3984-14693-7 PD" | NULL         |
|  4 |                3 | "3984-14693-7 PD" | NULL         |
|  5 |                3 | "3984-14639-1"    | NULL         |
|  6 |                5 | "3984-14639-1"    | NULL         |
|  7 |                3 | "BOBV136LP"       | NULL         |
|  8 |                4 | "BOBV136LP"       | "clear"      |
|  9 |                4 | "BOBV136LP"       | "blue"       |
| 10 |                4 | "BOBV136LP"       | "white"      |
| 11 |                6 | "3984-25050-1"    | "yellow/red" |
| 12 |                6 | "3984-25050-1"    | "orange"     |
| 13 |                6 | "3984-25050-1"    | "brown"      |
| 14 |                5 | "3984-25050-1"    | "blue"       |
| 15 |                6 | "3984-25050-1"    | "green"      |
| 16 |                6 | "3984-25050-1"    | "clear"      |
+----+------------------+-------------------+--------------+
16 rows in set (0,00 sec)


Recherche par valeur d'une propriété

Du coup, avec JSON_EXTRACT, le filtrage des disques par la valeur d'une propriété est très facile

mysql> SELECT id, properties FROM disc 
WHERE JSON_EXTRACT(properties, '$.color') = 'clear';
+----+----------------------------------------------------------------------------------------------------------------------+
| id | properties                                                                                                           |
+----+----------------------------------------------------------------------------------------------------------------------+
|  8 | {"color": "clear", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                   |
| 16 | {"color": "clear", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"} |
+----+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)

Cette méthode fonctionne très bien aussi pour les propriétés contenant un chiffre, comme la quantité dans notre exemple

mysql> SELECT id, properties FROM disc 
WHERE JSON_EXTRACT(properties, '$.quantity') > 500;
+----+-----------------------------------------------------------------------------------------------------+
| id | properties                                                                                          |
+----+-----------------------------------------------------------------------------------------------------+
|  1 | {"limited": true, "quantity": 1500, "reference": "3984-14639-1", "number_of_disc": 2}               |
|  6 | {"limited": true, "picture": true, "numbered": true, "quantity": 2000, "reference": "3984-14639-1"} |
+----+-----------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)
 

Idem pour les booléen, cela fonctionne mais il faut utiliser true ou false et surtout pas sous la forme d'une string comme la première requête de l'exemple.

mysql> SELECT id, properties FROM disc 
WHERE JSON_EXTRACT(properties, '$.test_pressing') = 'true';
Empty set (0,00 sec)
 
mysql> SELECT id, properties FROM disc 
WHERE JSON_EXTRACT(properties, '$.test_pressing') = true;
+----+--------------------------------------------------------------------------+
| id | properties                                                               |
+----+--------------------------------------------------------------------------+
|  2 | {"picture": true, "reference": "3984-14693-7 PD", "test_pressing": true} |
|  5 | {"picture": true, "reference": "3984-14639-1", "test_pressing": true}    |
|  7 | {"reference": "BOBV136LP", "test_pressing": true, "number_of_disc": 2}   |
+----+--------------------------------------------------------------------------+
3 rows in set (0,00 sec)

Pour rechercher selon une valeur de propriété sans préciser son nom, on peut utilise JSON_SEARCH (voir l'article sur les tableau sur plus d'explications sur cette fonction).

mysql> SELECT id, properties FROM disc 
WHERE JSON_SEARCH(properties, 'one', 'blue') IS NOT NULL;
+----+---------------------------------------------------------------------------------------------------+
| id | properties                                                                                        |
+----+---------------------------------------------------------------------------------------------------+
|  9 | {"color": "blue", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                 |
| 14 | {"color": "blue", "limited": true, "reissue": true, "quantity": 500, "reference": "3984-25050-1"} |
+----+---------------------------------------------------------------------------------------------------+
2 rows in set (0,00 sec)
 


Recherche par présence d'une ou plusieurs propriétés

Pour récupérer les disques qui ont une propriété particulière (quelque soit la valeur), ce sera JSON_CONTAINS_PATH. Le deuxième argument de la fonction est un mot-clé qui peut être "one" ou "all", dans le cas où on ne recherche qu'une seule propriété, la valeur n'a pas d'importance.

mysql> SELECT id, properties FROM disc 
WHERE JSON_CONTAINS_PATH(properties, 'one', '$.test_pressing');
+----+--------------------------------------------------------------------------+
| id | properties                                                               |
+----+--------------------------------------------------------------------------+
|  2 | {"picture": true, "reference": "3984-14693-7 PD", "test_pressing": true} |
|  5 | {"picture": true, "reference": "3984-14639-1", "test_pressing": true}    |
|  7 | {"reference": "BOBV136LP", "test_pressing": true, "number_of_disc": 2}   |
+----+--------------------------------------------------------------------------+
3 rows in set (0,00 sec)
 

Cette fonction sert également pour récupérer les diques qui ont au moins une propriété d'une liste, toujours quelque soit la valeur.  Dans ce cas par contre, la valeur du second attribut est important, ici "one"

mysql> SELECT id, properties FROM disc 
WHERE JSON_CONTAINS_PATH(properties, 'one', '$.color', '$.numbered');
+----+---------------------------------------------------------------------------------------------------------------------------+
| id | properties                                                                                                                |
+----+---------------------------------------------------------------------------------------------------------------------------+
|  3 | {"limited": true, "picture": true, "numbered": true, "quantity": 500, "reference": "3984-14693-7 PD"}                     |
|  6 | {"limited": true, "picture": true, "numbered": true, "quantity": 2000, "reference": "3984-14639-1"}                       |
|  8 | {"color": "clear", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                        |
|  9 | {"color": "blue", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                         |
| 10 | {"color": "white", "limited": true, "reference": "BOBV136LP", "number_of_disc": 2}                                        |
| 11 | {"color": "yellow/red", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"} |
| 12 | {"color": "orange", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}     |
| 13 | {"color": "brown", "limited": true, "reissue": true, "numbered": true, "quantity": 100, "reference": "3984-25050-1"}      |
| 14 | {"color": "blue", "limited": true, "reissue": true, "quantity": 500, "reference": "3984-25050-1"}                         |
| 15 | {"color": "green", "limited": true, "reissue": true, "numbered": true, "quantity": 300, "reference": "3984-25050-1"}      |
| 16 | {"color": "clear", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}      |
+----+---------------------------------------------------------------------------------------------------------------------------+
11 rows in set (0,00 sec)
 

Si le second argument est mis à "all" seuls les disques ayant toutes les propriétés de la liste seront sélectionnés.

mysql> SELECT id, properties FROM disc 
WHERE JSON_CONTAINS_PATH(properties, 'all', '$.color', '$.numbered', '$.limited');
+----+---------------------------------------------------------------------------------------------------------------------------+
| id | properties                                                                                                                |
+----+---------------------------------------------------------------------------------------------------------------------------+
| 11 | {"color": "yellow/red", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"} |
| 12 | {"color": "orange", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}     |
| 13 | {"color": "brown", "limited": true, "reissue": true, "numbered": true, "quantity": 100, "reference": "3984-25050-1"}      |
| 15 | {"color": "green", "limited": true, "reissue": true, "numbered": true, "quantity": 300, "reference": "3984-25050-1"}      |
| 16 | {"color": "clear", "limited": true, "reissue": true, "numbered": true, "quantity": 200, "reference": "3984-25050-1"}      |
+----+---------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0,00 sec)


Ces exemples sont relativement simples, le but est de comprendre le fonctionnement des fonctions JSON proposées par MySQL. On peut ensuite les utiliser sur des structures plus complexes comme des tableaux d'objet ou encore des objets imbriqués. Dans ce dernier cas, on a la possibilité de manipuler l'objet JSON sur la profondeur souhaitée en jouant avec le niveau ( le "$") dans les arguments des fonctions.

Il y a un commentaire.

Ecrit par Phil le 18 juin 2023

Merci !

Ajouter un commentaire