Working with JSON arrays in MySQL/MariaDB

Rubik cubes
Rubik cubes: arrays of arrays!

When you write stored procedures in MySQL or MariaDB, one of the features you may miss is arrays. At least, at a superficial look – because an array data type is actually not implemented.

But actually JSON support was added in MySQL 8.0 and in MariaDB 10.2, and JSON can contain arrays. We already used this characteristic in a previous article, using an array to implement a function that accepts any number of parameters.

Here we will see a more in details, and with some examples, how to work with JSON arrays: how to create arrays, how to loop over them, how to perform the most common operations. There will also be a general purpose function as an example.

Build a new array

You could compose a JSON array as a string. But I suggest to use the JSON_ARRAY() function instead, because it is less error prone and less verbose:

MariaDB [(none)]> SELECT JSON_ARRAY(1, 2, 3);
+---------------------+
| JSON_ARRAY(1, 2, 3) |
+---------------------+
| [1, 2, 3]           |
+---------------------+

Get the first and the last item

To access a single item from an array, you can use JSON_EXTRACT(). It accepts two parameters:

  • Any valid JSON document;
  • A path to a single element, written as a string.

As you can see, this function was not written specifically for arrays. However it works, as long as the array is valid JSON. To access the first item of an array:

SET @arr := JSON_ARRAY(10, 20, 30);
SELECT JSON_EXTRACT(@arr, '$[0]');

In other words, you simply use $[N], where N is the index of the element, starting from 0.

To get the last item, if we don’t know the array length, we can use JSON_LENGTH():

SET @arr := JSON_ARRAY(10, 20, 30);
SET @last := CONCAT('$[', JSON_LENGTH(@arr) - 1, ']');
SELECT JSON_EXTRACT(@arr, @last);

Note that it is possible to create empty arrays:

MariaDB [(none)]> SELECT JSON_ARRAY();
+--------------+
| JSON_ARRAY() |
+--------------+
| []           |
+--------------+

Add elements to an array

The JSON_ARRAY_INSERT() function adds an element at the specified position. If this position is already taken by another element, it will shift by one. To specify the position, we can use the same syntax used by JSON_EXTRACT().

Add an element at the beginning:

MariaDB [(none)]> SELECT @arr := JSON_ARRAY(100, 200, 300) AS my_array;
+-----------------+
| my_array        |
+-----------------+
| [100, 200, 300] |
+-----------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SELECT @arr := JSON_ARRAY_INSERT(@arr, '$[0]', 'X') AS my_array;
+----------------------+
| my_array             |
+----------------------+
| ["X", 100, 200, 300] |
+----------------------+
1 row in set (0.001 sec)

To add an element at the end when we don’t know the number of elements in advance:

MariaDB [(none)]> SELECT @arr := JSON_ARRAY(100, 200, 300) AS my_array;
+-----------------+
| my_array        |
+-----------------+
| [100, 200, 300] |
+-----------------+
1 row in set (0.000 sec)

MariaDB [(none)]> SET @last := CONCAT('$[', JSON_LENGTH(@arr), ']');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT @arr := JSON_ARRAY_INSERT(@arr, @last, 'X') AS my_array;
+----------------------+
| my_array             |
+----------------------+
| [100, 200, 300, "X"] |
+----------------------+
1 row in set (0.000 sec)

Actually, you could also specify a high number instead and the result will be the same. For example, if you are sure, that the array never has more than 5 items, you can use:

SELECT @arr := JSON_ARRAY(100, 200, 300) AS my_array;
SELECT @arr := JSON_ARRAY_INSERT(@arr, '$[100]', 'X') AS my_array;

Other writes

Other array write functions are more or less what you would expect if you know at least one programming language. This post is not going to cover every possible array operation, however this is a list of the function you will most likely need if you are going to work with arrays:

  • JSON_UNQUOTE() – Needed to read a JSON string, unless we want it wrapped in double quotes.
    • Or you can use the ->> operator, but this is not supported by MariaDB.
  • JSON_REPLACE() – Replace a value with another; if the original value does not exist, does nothing.
  • JSON_REMOVE() – Delete an item; the following items will shift by one position.
  • JSON_MERGE() – Appends an array’s items to another array.

Functions that don’t contain the word 'ARRAY' are also useful when working with objects. However, here we are only considering arrays.

Iterate over a JSON array

There is no built-in syntax to iterate over an array. However, we can do it by using a normal loop and the JSON_EXTRACT() function. Here is an example:

CREATE /*M! OR REPLACE */ PROCEDURE foreach_array_item(
        in_array BLOB,
        in_callback VARCHAR(64)
    )
    NOT DETERMINISTIC
    MODIFIES SQL DATA
    COMMENT
    'Iterate an array and for each item invoke a callback procedure'
BEGIN
    DECLARE i INT UNSIGNED
        DEFAULT 0;
    DECLARE v_count INT UNSIGNED
        DEFAULT JSON_LENGTH(in_array);
    DECLARE v_current_item 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_current_item :=
            JSON_EXTRACT(in_array, CONCAT('$[', i, ']'));
        SET @sql_array_callback :=
            CONCAT('CALL ', in_callback, '(', v_current_item, ');');
        
        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 written for MariaDB. To run it in MySQL, the in_array parameter should be of type JSON instead of BLOB.

To test it, let's build a dummy procedure and invoke it properly:

CREATE /*M! OR REPLACE */ PROCEDURE do_something(p_something BLOB)
    DETERMINISTIC
    NO SQL
BEGIN
    SELECT p_something AS something;
END;

-- let's invoke it with different data types
CALL foreach_array_item(JSON_ARRAY(
    100, 200.2, 'X', DATE '1994-01-01'
), 'do_something');
+-----------+
| something |
+-----------+
| 100       |
+-----------+
1 row in set (0.01 sec)

+-----------+
| something |
+-----------+
| 200.2     |
+-----------+
1 row in set (0.01 sec)

+-----------+
| something |
+-----------+
| X         |
+-----------+
1 row in set (0.01 sec)

+------------+
| something  |
+------------+
| 1994-01-01 |
+------------+
1 row in set (0.01 sec)

And more?

Again: this page cannot cover the whole topic. But if you have code, ideas or tricks to share, please feel absolutely free to do it in a comment. Or maybe ask me to dig into some details you are interested in, and I'll see what I can do. After all, this post was written after a request that I received privately.

Toodle pip,
Federico

Photo credit: Gerwin Sturm

Comments (5)

  1. Hello,
    Good article, simple enough to be understood by many. Have given me some ideas for pushing mariadb’s upgrade to the Infra Team.

    Just two typos:
    In first use of the last element of array, the JSON_EXTRACT did not use the @last variable .
    And in the “iterate” example, the info on how to change from mariadb to mysql seems false (bad variable or bad type)

    1. Hi Piotr!
      First of all, thanks for the mistake. I fixed the first. The second, in my opinion, is that the code was actually the MySQL version, not the MariaDB one. I fixed it. Try it again, it should works (it does for me with MySQL 8 and MariaDB 10.3).

      An infrateam usually wants to hear that the performance will not degrade switching to MariaDB, and that they can continue to use their current tools and procedures. Which is true… except when it isn’t 🙂 But every upgrade – even from MySQL 5.7 to 8.0 – hides potential issues.

  2. If I wanted to be extra super nit-picky I could say that JSON in MariaDB is not an alias for BLOB, but an alias for LONGTEXT. A BLOB column can store 65,535 bytes, whereas a LONGTEXT can store 4GB characters (though the effective length is shorter if there are multi-byte characters …)

    Anyway, thank you for writing up and sharing this article, very helpful as usual! Does the “/*M! … */” syntax do what I think it does, i.e. execute only in MariaDB and is ignored elsewhere?

    1. Karl, thank you for your comment!
      I am open to discuss improvement to my articles and code. Though, my reasoning is:
      * Do we really want to pass up to 4G to JSON functions? I was even thinking to use something smaller than BLOB.
      * TEXT behaviour depends a bit more on the character set/collation settings, while BLOB is universal. I didn’t find any situation where BLOB is bad for working with pure JSON. But if you know of some drawback, I will be happy to update the article!

      Yes, /*M! … */ is a MariaDB “executable comment”, so it’s ignored by MySQL. It’s a pity that there is no way to do the opposite.
      You just gave me an idea for the next post.

Leave a Reply

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