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
Ajouter un commentaire