Working with JSON arrays in MySQL/MariaDB

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 aprevious 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), ']');
SELECT JSON_EXTRACT(@arr, '$[0]');

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 JSON,
        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