Working with JSON arrays in MySQL/MariaDB

Last Updated on

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, ']');

Note that it is possible to create empty arrays:

MariaDB [(none)]> SELECT 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)
    'Iterate an array and for each item invoke a callback procedure'
        DEFAULT 0;
        DEFAULT JSON_LENGTH(in_array);
    DECLARE v_current_item BLOB

    -- 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;

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)
    NO SQL
    SELECT p_something AS something;

-- 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)

See also

Related articles:

Related courses:

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,

Photo credit: Gerwin Sturm

Comments (15)

  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.

  3. I’m not sure you covered this in a different article, but searching is important, and using the “JSON_SEARCH” can reduce the iteration to a single line.

    1. Hi Daniel,
      Foreign keys are build on columns, not value. So no, you can’t put foreign keys inside an array.
      If your array is somehow “fixed”, for example if it contains at most 3 values, you can build 3 generated columns on them, and then build foreign keys on these columns. But then I don’t think that using an array would be useful…


  4. Hi Federico, thanks for the article. One question: let’s suppose that you have an array of cities [“Rome”, “New York”, “Paris”] and you want to change “New York” for “London” without knowing the position of “New York” in the array. How would you do the replacement? (Thank you in advance)

    1. Hi Daniel!
      You’ll have to use the JSON_REPLACE() function. The query could be quite slow, if your table isn’t small-ish.
      However MySQL supports indexes on JSON arrays. If it’s something you’ll do often, an index will help.


      1. Hi Federico!!
        Thanks for your reply. What I’m using at the moment is JSON_SEARCH, JSON_REMOVE and JSON_SET: It is not known the position of the city in the array, which seems to be mandatory in JSON_REPLACE. Do you have a working example of how could it be done with JSON_REPLACE?

        1. Sorry for the late reply. You’re right about JSON_REPLACE(). Why do you want to do this, in the first place? It is not a good way to use a database.
          Anyway, the only way to do it seems to use a loop.

          1. Hi Federico, just saw your reply, thanks for it. The use case is a JSON `details` column in a users table that stores miscellaneous information on a user (like her favourite cities, phone numbers, email addresses, social media nicknames, etc.). This all could be normalized in different tables, but a JSON column seems like a good match for gathering all these data. What I”m doing now to solve the question that I made is JSON_REPLACE with the full array, not looking anymore to replace just one item, and it works. What do you think of this approach? Is it pushing too much the use of a JSON column?

Leave a Reply

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