Manipulate MySQL 8 JSON colums Part 2: simple object
After the first post who shows the possibility of the JSON functions with MySQL 8 on a JSON array, let's talk about what we can achieve with a JSON object.
Example case
A JSON object could replace an EAV (Entity Attribute Value) who can be difficult to manipulate by something lighter. For those who do not know the concept, it is a data model to save a large number of possible attributes for an entity when these are not all filled in. Usually there is a table for the entity, a table for the list of possible attributes and a binding table containing the entity identifier, the attribute identifier and its value.
For this article, let's considerate a database of disc. The optional attributes like the fact the disc is a promo version or limited or colored, are stored in a column properties.
The table would look like this
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)
and here the data
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)
Data extraction
In case of a JSON object, the function JSON_LENGTH returns the number of properties in the JSON and the function JSON_EXTRACT allows you to retrieve the value of a specific property (like the reference or the color for example), whether it is present in the JSON or not.
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)
Search by property value
With JSON_EXTRACT, it's really easy to filter the disc by a propertry value.
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)
It works also very well for properties containing a number, like the quantity in our example.
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)
It works also for the boolean, but we have to use the keyword true and false and not as strings.
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)
If we want to search for a property value but without knowing the name of the property, we can use JSON_SEARCH function (more explanations of the function in the first article about JSON array).
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)
Search by presence of one or several properties
To get the discs who have a specific property (regardless of the value), we'll need JSON_CONTAIN_PATH function. The second argument of the function is a keyword who can be "one" or "all". In the case where we are only looking for a single property, the value does not matter.
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)
This function allows us also to get discs who have at least one property among a list of properties, still regardless of the value. In this case, the value of the second argument is import and must be "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)
If the second argument is "all", only the discs with all the properties in the list will be selected.
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)
These examples are relatively simple, the goal is to understand how the JSON functions offered by MySQL 8 work. They can then be used on more complex structures such as object arrays or nested objects. In the later case, we have the possibility of manipulating the JSON object to the desired depth by playing with the level (the "$") in the function arguments.
Add a comment