Manipulate MySQL8 JSON colums Part 1: simple array
MySQL 8 has introduced an advanced support of JSON type and functions. The documentation is complete for the functions but I found the examples not very helpful and too much theorical.
The idea here is to show, with a real, but simplified, database some things we can do with this format.
In this first article, we will manipulate a JSON colums containing a simple array, which means an array with only one level, to keep this article readable.
Example case
In which case we have a mysql table with a column containing a list as a JSON array? This could replace a N-N table when we don't need to be able to have the relation in both ways. In this case, the table with the relation would have a JSON column containing a list of references to the other table, the PK (Primary Key) or another identifier (like a uuid).
In my example, for readability, I will store understandable strings.
Let's consider a database of concerts. We have a "concert" table with (among other things) a setlist column containing the titles played during the concert. We have also a "song" table with all the titles. As said before, for more readability, the name of the songs are store inside the setlist column instead of identifiers.
The 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)
The data
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)
Manipulate according to table size
The function JSON_LENGTH return the size of a JSON, so we can sort our concerts according to the number of songs played.
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)
Search for one element
To find all concerts with a specific song in the setlist we have several options.
For an exact search, we can use 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)
The second argument of the function is the value we are looking for and must be a JSON, that's why the song is in an array.
The "$" in third argument indicated the level inside the JSON where the function must be applied. Here as we have only one level, the function applied at the root of the JSON. If we had an array of array and we want to search inside the first level of the array, the third argument of the function will be "$[0]".
Another way to do this search is to use the MEMBER OF function
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)
The last solution, who in addition, can perform a inexact search, is to use the JSON_SEARCH function. The function doesn't not return a boolean like the other two but a list of the positions in the array where the sought element was found. The function stops at the first path found if the keyword "one" is the second argument of the function, or analyse all the elements if the argument is "all". The thirst argument is the sought string and work as LIKE function.
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)
So, in order to find the concerts with a specific song in its setlist, with the full title or just a part of it, we can filter concerts where the JSON_SEARCH function returns result.
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)
Search for a list of elements
If we have a list of song, and we want the concerts where all the songs of the list was played, the only way is to used the JSON_CONTAINS function, like we did for the search of one title.
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)
But if we want to retrieve the concerts where at least one of the song of the list was played, we need the JSON_OVERLAPS function.
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)
Let's try something a little more complicated. We don't have the list of songs, but we can get it from the song table. We could want to find the concerts with a least one song of a specific album, or a specific band.
To do that, we will need the JSON_ARRAYAGG function who will transform the result of a query in a JSON array we could put as second argument of 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)
JOIN on the JSON
At last, in order to return the song information for a setlist, we will need the functions JSON_CONTAINS and 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)
Another way is to split the JSON to x lines first, one for each entry in the JSON. For that, there is the function 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)
The declaration is a little complicated. The first argument is a JSON array. The second begins with an expression to match the elements in the array who will be treated, here all of them. Then we have the declaration of the column or columns created with theses elements. If the JSON is an array of object, we could generate severel column, but here we have only one.
Then, it's easy to add our join.
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)
Beware to declare the column in JSON_TABLE exactly like in the join table, encoding included.
That's all for today! I hope I was able to show you the possibility of the JSON column with MySQL 8. In the next article, we will manipulate a JSON object.
Add a comment