Manipulation des colonnes JSON avec MySQL 8 Part 1: tableaux simples
MySQL, avec sa version 8 a introduit un support avancé du type et des fonctions JSON. La documentation est complète et présente toutes les fonctions, mais je trouve que les exemples ne sont pas très parlant et n'aident pas à savoir quelle fonction utiliser selon le besoin.
J'ai donc voulu, à partir d'une base de donnée simplifiée, montrer quelques exemples de ce que ce format permet de faire.
Pour ce premier article, je vais traiter le cas d'un JSON contenant un tableau simple, c'est-à-dire à un seul niveau, afin de garder l'article d'une taille raisonnable.
Cas d'exemple
Dans quel cas pourrait-on avoir dans une table mysql une colonne contenant une liste sous forme de tableau JSON? Cela peut remplacer une liaison N-N lorsque l'on est intéressé que par
la liaison dans un seul sens. Dans ce cas, la table portant la liaison
aura un champ JSON contenant un liste de références à l'autre table, de
préférence la PK (Primary Key) ou un autre identifiant (type uuid).
Dans
le cas de mon exemple, pour avoir quelque chose de plus lisible
humainement, je vais stocker des chaines de caractères compréhensibles.
Pour mon exemple considérons une base de concerts. Nous avons une table concert contenant (entre autre) une colonne setlist contenant les titres joués. Nous avons aussi une table song contenant tous les titres avec diverses informations. Comme indiqué au dessus, pour rendre cela plus lisible j'ai mis les titres dans la colonne setlist plutôt que leurs identifiants.
Les tables
mysql> show create table concert\G *************************** 1. row *************************** Table: concert Create Table: CREATE TABLE `concert` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `band` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `date` date NOT NULL, `location` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `setlist` json NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0,00 sec) mysql> show create table song\G *************************** 1. row *************************** Table: song Create Table: CREATE TABLE `song` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `band` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `album` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0,00 sec)
Et les données de départ
mysql> SELECT * FROM concert; +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | band | date | location | setlist | +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | Metallica | 2020-11-14 | HQ, San Rafael | ["Blackened", "Creeping Death", "When a Blind Man Cries", "The Unforgiven", "Now That We are Dead", "Turn The Page", "Nothing Else Matters", "All Within My Hands"] | | 2 | Metallica | 2019-09-08 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | Metallica | 2018-09-16 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Metallica | 2015-06-04 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0,00 sec) mysql> select * from song; +----+-----------------+---------------------------+----------------------------+ | id | band | name | album | +----+-----------------+---------------------------+----------------------------+ | 1 | Metallica | Blackened | Metallica | | 2 | Metallica | Creeping Death | Ride The Lightning | | 3 | Deep Purple | When a Blind Man Cries | NULL | | 4 | Metallica | The Unforgiven | Metallica | | 5 | Metallica | Now That We are Dead | Hardwired To Self Destruct | | 6 | Bob Seger | Turn The Page | NULL | | 7 | Metallica | Nothing Else Matters | Metallica | | 8 | Metallica | All Within My Hands | Saint Anger | | 9 | Ennio Morricone | The Ecstasy of Gold | NULL | | 10 | Metallica | The Call of Ktulu | Ride The Lightning | | 11 | Metallica | For Whom the Bell Tolls | Ride The Lightning | | 12 | Metallica | The Day That Never Comes | Death Magnetic | | 13 | Metallica | The Memory Remains | Reload | | 14 | Metallica | Confusion | Hardwired To Self Destruct | | 15 | Metallica | Moth Into Flame | Hardwired To Self Destruct | | 16 | Metallica | The Outlaw Torn | Load | | 17 | Metallica | No Leaf Clover | S&M | | 18 | Metallica | Halo On Fire | Hardwired To Self Destruct | | 19 | Metallica | Harwired | Hardwired To Self Destruct | | 20 | Metallica | Atlas, Rise | Hardwired To Self Destruct | | 21 | Metallica | Seek & Destroy | Kill Em All | | 22 | Metallica | Harvester Of Sorrow | And Justice For All | | 23 | Misfits | Last Caress | NULL | | 24 | Metallica | Fuel | Reload | | 25 | Metallica | No Remorse | Kill Em All | | 26 | Metallica | King Nothing | Load | | 27 | Metallica | Disposable Heroes | Master Of Puppets | | 28 | Metallica | The Unforgiven II | Death Magnetic | | 29 | Metallica | Cyanide | Death Magnetic | | 30 | Metallica | Lords Of Summer | NULL | | 31 | Metallica | Sad But True | Metallica | | 32 | Metallica | The Frayed Ends Of Sanity | And Justice For All | | 33 | Metallica | One | And Justice For All | | 34 | Metallica | Master Of Puppets | Master Of Puppets | +----+-----------------+---------------------------+----------------------------+ 34 rows in set (0,00 sec)
Manipuler par rapport à la taille du tableau
la fonction JSON_LENGTH retourne la taille d'un JSON, ce qui permet par exemple de trier nos concerts selon le nombre de titres joués
mysql> SELECT id, date, JSON_LENGTH(setlist), setlist FROM concert ORDER BY JSON_LENGTH(setlist); +----+------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | date | JSON_LENGTH(setlist) | setlist | +----+------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | 2020-11-14 | 8 | ["Blackened", "Creeping Death", "When a Blind Man Cries", "The Unforgiven", "Now That We are Dead", "Turn The Page", "Nothing Else Matters", "All Within My Hands"] | | 2 | 2019-09-08 | 10 | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | 2018-09-16 | 10 | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | 2015-06-04 | 12 | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+------------+----------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0,00 sec)
Recherche sur un élément de la liste
Pour retrouver les concerts dont la setlist contient un titre exact, plusieurs solutions s'offrent à nous.
Pour une recherche exacte, nous pouvons utiliser JSON_CONTAINS
mysql> SELECT id, location, setlist FROM concert WHERE JSON_CONTAINS(setlist, '["For Whom the Bell Tolls"]', '$'); +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | setlist | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0,00 sec)
Le second argument doit être au format JSON, c'est pour cela que le titre que l'on recherche a été mis dans un tableau à une seul entrée.
Le "$" en troisième argument de la méthode indique le niveau dans le JSON dans lequel la fonction doit s'appliquer. Ici comme nous avons un tableau simple, nous recerchons à la racine du JSON, si nous avions un tableau de tableau, nous pourrions vouloir rechercher que dans le premier élément du tableau, soit dans "$[0]".
Une autre manière de faire est d'utiliser la fonction MEMBER OF
mysql> SELECT id, location, setlist FROM concert WHERE "For Whom the Bell Tolls" MEMBER OF (setlist); +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | setlist | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0,00 sec)
Une dernière solution, qui permet en plus de faire une recherche non exacte est d'utiliser la fonction JSON_SEARCH. A la différence des 2 précédentes, cette fonction ne retourne pas un booléan mais une liste de position où l'élément recherché a été trouvé, la fonction s'arrête au premier élément trouvé si le mot-clé "one" est mis en second argument ou passe sur tous les éléments du JSON si "all" est indiqué en second argument. Le troisième argument contient la chaine de caractère recherché et fonctionne comme avec la fonction LIKE
mysql> SELECT id, location, JSON_SEARCH(setlist, 'one', '%on%'), setlist FROM concert; +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | JSON_SEARCH(setlist, 'one', '%on%') | setlist | +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | HQ, San Rafael | NULL | ["Blackened", "Creeping Death", "When a Blind Man Cries", "The Unforgiven", "Now That We are Dead", "Turn The Page", "Nothing Else Matters", "All Within My Hands"] | | 2 | Chase Center, San Francisco | "$[5]" | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | "$[5]" | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | NULL | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0,00 sec) mysql> SELECT id, location, JSON_SEARCH(setlist, 'all', '%on%'), setlist FROM concert; +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | JSON_SEARCH(setlist, 'all', '%on%') | setlist | +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | HQ, San Rafael | NULL | ["Blackened", "Creeping Death", "When a Blind Man Cries", "The Unforgiven", "Now That We are Dead", "Turn The Page", "Nothing Else Matters", "All Within My Hands"] | | 2 | Chase Center, San Francisco | "$[5]" | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | ["$[5]", "$[7]"] | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | NULL | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+-------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 4 rows in set (0,00 sec)
On peut donc retrouver les concerts contenant un titre spécifique, par son nom entier ou partiel en filtrant par les lignes où JSON_SEARCH renvoit un résultat
mysql> SELECT id, location, setlist FROM concert WHERE JSON_SEARCH(setlist, 'one', 'For Whom the Bell Tolls') IS NOT NULL; +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | setlist | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0,00 sec) mysql> SELECT id, location, setlist FROM concert WHERE JSON_SEARCH(setlist, 'one', '%Bell%') IS NOT NULL; +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | location | setlist | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | | 4 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0,00 sec)
Recherche sur une liste d'éléments
SI nous avons une liste de titres et que nous voulons trouver les concerts contenant tous les titres de cette liste, la solution est d'utiliser JSON_CONTAINS de la même manière que nous l'avons fait pour un seul titre
mysql> SELECT * FROM concert WHERE JSON_CONTAINS(setlist, '["Halo On Fire", "Confusion"]', '$'); +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | band | date | location | setlist | +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Metallica | 2019-09-08 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec)
Au contraire, si nous voulons les concerts ayant au moins un titre d'une liste, cette fois nous avons besoin de JSON_OVERLAPS
mysql> SELECT * FROM concert WHERE JSON_OVERLAPS(setlist, '["Halo On Fire", "Confusion"]'); +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | band | date | location | setlist | +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Metallica | 2019-09-08 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 3 | Metallica | 2018-09-16 | SAP Arena, Mannheim | ["Harwired", "Atlas, Rise", "Seek & Destroy", "Harvester Of Sorrow", "Now That We are Dead", "Confusion", "For Whom the Bell Tolls", "Halo on Fire", "Last Caress", "The Memory Remains"] | +----+-----------+------------+-----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0,00 sec)
Compliquons un peut les choses et disons que nous n'avons pas une liste de titres, mais que nous souhaitons récupérer cette liste de la table de titres, par exemple en voulant récupérer tous les titres d'un album ou d'un artiste et retrouver les concerts contenant au moins un titre de cette sélection.
Pour cela nous aurons besoin en plus de JSON_ARRAYAGG qui va nous permettre de transformer notre selection en un tableau JSON nécessaire au deuxième argument de JSON_OVERLAPS.
mysql> SELECT * FROM concert WHERE JSON_OVERLAPS(setlist, (SELECT JSON_ARRAYAGG(name) FROM song WHERE album = 'Load')); +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | band | date | location | setlist | +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2 | Metallica | 2019-09-08 | Chase Center, San Francisco | ["The Ecstasy of Gold", "The Call of Ktulu", "For Whom the Bell Tolls", "The Day That Never Comes", "The Memory Remains", "Confusion", "Moth Into Flame", "The Outlaw Torn", "No Leaf Clover", "Halo On Fire"] | | 4 | Metallica | 2015-06-04 | Vienna | ["Fuel", "For Whom the Bell Tolls", "No Remorse", "King Nothing", "Disposable Heroes", "The Unforgiven II", "Cyanide", "Lords Of Summer", "Sad But True", "The Frayed Ends Of Sanity", "One", "Master Of Puppets"] | +----+-----------+------------+-----------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0,01 sec) mysql> SELECT * FROM concert WHERE JSON_OVERLAPS(setlist, (SELECT JSON_ARRAYAGG(name) FROM song WHERE band = 'Deep Purple')); +----+-----------+------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | band | date | location | setlist | +----+-----------+------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | Metallica | 2020-11-14 | HQ, San Rafael | ["Blackened", "Creeping Death", "When a Blind Man Cries", "The Unforgiven", "Now That We are Dead", "Turn The Page", "Nothing Else Matters", "All Within My Hands"] | +----+-----------+------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,00 sec)
Jointure sur le JSON
Et enfin, si nous souhaitons retourner toutes les informations des titres d'une setlist, nous aurons besoin de JSON_CONTAINS et JSON_QUOTE.
mysql> SELECT concert.band, concert.date, song.band AS song_band, song.name, song.album FROM concert INNER JOIN song ON JSON_CONTAINS(concert.setlist, JSON_QUOTE(song.name), '$') WHERE concert.id = 1; +-----------+------------+-------------+------------------------+----------------------------+ | band | date | song_band | name | album | +-----------+------------+-------------+------------------------+----------------------------+ | Metallica | 2020-11-14 | Metallica | Blackened | Metallica | | Metallica | 2020-11-14 | Metallica | Creeping Death | Ride The Lightning | | Metallica | 2020-11-14 | Deep Purple | When a Blind Man Cries | NULL | | Metallica | 2020-11-14 | Metallica | The Unforgiven | Metallica | | Metallica | 2020-11-14 | Metallica | Now That We are Dead | Hardwired To Self Destruct | | Metallica | 2020-11-14 | Bob Seger | Turn The Page | NULL | | Metallica | 2020-11-14 | Metallica | Nothing Else Matters | Metallica | | Metallica | 2020-11-14 | Metallica | All Within My Hands | Saint Anger | +-----------+------------+-------------+------------------------+----------------------------+ 8 rows in set (0,01 sec)
Une autre solution est de commencer par séparer le JSON en x lignes, une pour chaque entrée du JSON. On peut faire cela avec JSON_TABLE.
mysql> SELECT band, date, song FROM concert, JSON_TABLE(setlist, '$[*]' COLUMNS (song VARCHAR(255) PATH '$')) tmp WHERE id = 1; +-----------+---------------------+------------------------+ | band | date | song | +-----------+---------------------+------------------------+ | Metallica | 2020-11-14 00:00:00 | Blackened | | Metallica | 2020-11-14 00:00:00 | Creeping Death | | Metallica | 2020-11-14 00:00:00 | When a Blind Man Cries | | Metallica | 2020-11-14 00:00:00 | The Unforgiven | | Metallica | 2020-11-14 00:00:00 | Now That We are Dead | | Metallica | 2020-11-14 00:00:00 | Turn The Page | | Metallica | 2020-11-14 00:00:00 | Nothing Else Matters | | Metallica | 2020-11-14 00:00:00 | All Within My Hands | +-----------+---------------------+------------------------+ 8 rows in set (0,00 sec)
La déclaration de cette fonction est un peu particulière, le premier argument est un tableau JSON. Le second commence par une expression indiquant quels éléments du tableau doivent être traités, ici tous, puis la déclaration de la ou des colonnes créées avec ces éléments. Si le JSON est un tableau d'objets, la fonction pourrait générer plusieurs colonnes selon les attributs de l'objet dans le tableau.
A partir de là, nous pouvons ajouter notre jointure
mysql> SELECT concert.band, concert.date, tmp.song, song.album FROM concert, JSON_TABLE(setlist, '$[*]' COLUMNS (song VARCHAR(255) PATH '$')) tmp INNER JOIN song ON tmp.song = song.name WHERE concert.id = 1; +-----------+---------------------+------------------------+----------------------------+ | band | date | song | album | +-----------+---------------------+------------------------+----------------------------+ | Metallica | 2020-11-14 00:00:00 | Blackened | Metallica | | Metallica | 2020-11-14 00:00:00 | Creeping Death | Ride The Lightning | | Metallica | 2020-11-14 00:00:00 | When a Blind Man Cries | NULL | | Metallica | 2020-11-14 00:00:00 | The Unforgiven | Metallica | | Metallica | 2020-11-14 00:00:00 | Now That We are Dead | Hardwired To Self Destruct | | Metallica | 2020-11-14 00:00:00 | Turn The Page | NULL | | Metallica | 2020-11-14 00:00:00 | Nothing Else Matters | Metallica | | Metallica | 2020-11-14 00:00:00 | All Within My Hands | Saint Anger | +-----------+---------------------+------------------------+----------------------------+ 8 rows in set (0,00 sec)
Attention, il faut que la colonne déclarer dans le JSON_TABLE soit bien du même type que la colonne doit la table jointe, encodage compris.
Voilà, j'espère que cela vous a donné un aperçu des possibilités du JSON avec MySQL 8. Dans le prochain article, nous manipulerons un objet JSON dans MySQL.
Réponse de Ulrich le 28 mars 2022