Working with JSON objects in MySQL/MariaDB

Random work tools on a table
Store your objects on a table and work with them

Some time ago, I’ve published an article about Working with JSON arrays in MySQL/MariaDB. In that article I’ve showed how – even if MySQL and MariaDB don’t support native arrays – they provide all the necessary functions to work with JSON arrays. You can create them, add elements, drop elements, read an element, iterate them, and so on… use them as actual arrays.

This may surprise you or not. But once you find that out, it shouldn’t be surprising that you can use JSON objects to simulate native objects. If you’ve read the article about how to simulate optional parameters in stored procedures, you probably remember that we did exactly that. We’ve put optional parameters into a JSON object. Since JSON documents are schemaless, each key can exist or not. And if it exists, it has a value and an associated name.

This post shows what can be done with JSON objects, and some examples.

Creating objects

Objects can be created with the JSON_OBJECT() function. For each item we want to be in the array, we will pass a key and a value:

mysql> -- if a person with this name exists,
mysql> -- it could be a coincidence
mysql> SELECT JSON_OBJECT(
    ->     'full_name', 'Boris Johnson',
    ->     'QI', 0
    -> ) AS brexit_guru;
+-----------------------------------------+
| brexit_guru                             |
+-----------------------------------------+
| {"QI": 0, "full_name": "Boris Johnson"} |
+-----------------------------------------+

As you can see, SQL types are converted to the closest JSON type.

Objects can be nested. In the following example, we will use JSON_PRETTY() to print the resulting object in a human-readable way (useful for debug).

mysql> -- if a time lord with this nickname exists,
mysql> -- it could be a coincidence
mysql> mysql> SET @time_lord := JSON_OBJECT(
    ->     'name', JSON_OBJECT(
    ->         'first', NULL,
    ->         'last', NULL,
    ->         'nickname', 'The Doctor'
    ->     ),
    ->     'enemies', JSON_ARRAY(
    ->         'Daleks',
    ->         'Cybermen'
    ->     )
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT JSON_PRETTY(@time_lord) AS time_lord \G
*************************** 1. row ***************************
time_lord: {
  "name": {
    "last": null,
    "first": null,
    "nickname": "The Doctor"
  },
  "enemies": [
    "Daleks",
    "Cybermen"
  ]
}
1 row in set (0.00 sec)

Finally, it’s worth nothing that we can create empty objects that you can populate later (just like we can create empty arrays):

mysql> SELECT JSON_OBJECT() AS obj, JSON_ARRAY() AS arr;
+------+------+
| obj  | arr  |
+------+------+
| {}   | []   |
+------+------+

Reading an object

To get an object’s keys, we can use JSON_KEYS():

mysql> SELECT JSON_KEYS(@time_lord);
+-----------------------+
| JSON_KEYS(@time_lord) |
+-----------------------+
| ["name", "enemies"]   |
+-----------------------+

We can read an element with JSON_EXTRACT(). You probably remember that this is the same function used to read array elements. For objects, the syntax is slightly different:

mysql> SELECT JSON_EXTRACT(@time_lord, '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT(@time_lord, '$.name')                      |
+---------------------------------------------------------+
| {"last": null, "first": null, "nickname": "The Doctor"} |
+---------------------------------------------------------+

In this case, an object is returned. But we can read an element from a nested object with a single call:

mysql> SELECT JSON_TYPE(JSON_EXTRACT(@time_lord, '$.name.nickname'));
+---------------------------------------------+
| JSON_EXTRACT(@time_lord, '$.name.nickname') |
+---------------------------------------------+
| "The Doctor"                                |
+---------------------------------------------+

If we want to know the element’s type, we can use JSON_TYPE():

mysql> SELECT JSON_TYPE(JSON_EXTRACT(@time_lord, '$.name.nickname'));
+--------------------------------------------------------+
| JSON_TYPE(JSON_EXTRACT(@time_lord, '$.name.nickname')) |
+--------------------------------------------------------+
| STRING                                                 |
+--------------------------------------------------------+

Adding, removing, modifying values

Now, suppose that we want to change the name property and add a gender property. We can use JSON_SET(), which adds or replaces an element:

SET @time_lord := JSON_SET(@time_lord, '$.name.nickname', 'Missy');
SET @time_lord := JSON_SET(@time_lord, '$.gender', 'F');

To modify items we can also use JSON_REPLACE(), which has no effect if the element does not exist:

SET @time_lord := JSON_REPLACE(@time_lord, '$.name.nickname', 'The Master');

We can also decide to remove the properties that are set to null:

SET @time_lord := JSON_REMOVE(@time_lord, '$.name.first');
SET @time_lord := JSON_REMOVE(@time_lord, '$.name.last');

Finally, let’s check if the result is what we expect:

mysql> SELECT JSON_EXTRACT(@time_lord, '$');
+----------------------------------------------------------------------------------------+
| JSON_EXTRACT(@time_lord, '$')                                                          |
+----------------------------------------------------------------------------------------+
| {"name": {"nickname": "The Master"}, "gender": "F", "enemies": ["Daleks", "Cybermen"]} |
+----------------------------------------------------------------------------------------+

Jolly good!

Merging objects and arrays

I won’t dig too much into this topic, because merging JSON objects and arrays in MySQL is not something that one does often, in my opinion. But if you think I’m wrong, please comment this post and explain the use cases. I could even explore this topic in a future article.

There are two functions to merge objects and arrays. I won’t explain the details and corner cases, but the general idea is:

  • JSON_MERGE_PATCH() merges two or more objects into a new object. If the same key occurs in multiple objects, only one value will be in the output object.
  • JSON_MERGE_PRESERVE() merges the objects preserving all the values. If a key “name” appears in two objects and its values are ‘Rose’ and ‘Clara’, in the resulting object the value will be an array: [‘Rose’, ‘Clara’].

Iterate over an object

In a programming language, something we want to be able to do is to iterate an object properties. In MySQL’s SQL this is a bit tricky, but perfectly doable.

We already discussed how to iterate an array, by composing the proper strings to pass to JSON_EXTRACT(). This time, we will do this with the array returned by JSON_KEYS(), which is the list of the object keys. For each key in the object, we’ll get the corresponding value.

CREATE /*M! OR REPLACE */ PROCEDURE foreach_array_item(
        in_object BLOB,
        in_callback VARCHAR(64)
    )
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    COMMENT
    'Iterate an object and for each item invoke a callback procedure'
BEGIN
    DECLARE v_key_list BLOB
        DEFAULT JSON_KEYS(in_object);
    DECLARE i INT UNSIGNED
        DEFAULT 0;
    DECLARE v_count INT UNSIGNED
        DEFAULT JSON_LENGTH(v_key_list);
    DECLARE v_key BLOB
        DEFAULT NULL;
    DECLARE v_value BLOB
        DEFAULT NULL;

    -- loop from 0 to the last item
    WHILE i < v_count DO
        -- get the current item and build an SQL statement
        -- to pass it to a callback procedure
        SET v_key :=
            JSON_EXTRACT(v_key_list, CONCAT('$[', i, ']'));
        SET v_value :=
            JSON_EXTRACT(in_object, CONCAT('$.', v_key));
        SET @sql_array_callback :=
            CONCAT('CALL ', in_callback, '(', v_key, ', ', v_value, ');');
        
        PREPARE stmt_array_callback FROM @sql_array_callback;
        EXECUTE stmt_array_callback;
        DEALLOCATE PREPARE stmt_array_callback;

        SET i := i + 1;
    END WHILE;
END

This procedure is actually very similar to the one we created to iterate arrays. And we will test it in a similar way. We are creating a callback function that just SELECTs the key and value of each element of the object. We will test it with different datatypes.

CREATE /*M! OR REPLACE */ PROCEDURE do_something(p_key VARCHAR(50), p_value BLOB)
    DETERMINISTIC
    NO SQL
BEGIN
    SELECT p_key AS `key`, p_value as `value`;
END

MariaDB [test]> CALL foreach_array_item(JSON_OBJECT(
    ->     'where', 'Mexico',
    ->     'when', DATE '1994-01-01'
    -> ), 'do_something');
+-------+--------+
| key   | value  |
+-------+--------+
| where | Mexico |
+-------+--------+
1 row in set (0.002 sec)

+------+------------+
| key  | value      |
+------+------------+
| when | 1994-01-01 |
+------+------------+
1 row in set (0.002 sec)

Yoo-hoo, another success!

Related articles

Share your ideas

Please, share your ideas! That's what I did here, and one of the purposes of a community.

If you have suggestions to better use objects in a database, or new creative uses for them, or something like that, please write a comment. Or, if you prefer, write your own blog post and post a link here.

Let's keep in touch.

Toodle pip,
Federico

Photo credit

Comments (2)

Leave a Reply

Your email address will not be published. Required fields are marked *