Use JSON_EXTRACT function to extract elements in JSON array in MariaDB

MariaDB JSON_EXTRACT() function is used to extract elements from JSON data. This function takes two parameters, the first parameter is the JSON document to extract from, and the second parameter is the path expression to extract.

If you want to extract elements from a JSON array, use the array element selector:

• [N]: Select elements according to the specified index N, starting from 0.
• [*]: Selects all elements in the array.

Example 1: Simple Array

Here is a simple example to demonstrate how to select a single array element:

SET @json = '[0,1,2,3]';
SELECT
  JSON_EXTRACT(@json, '$[0]') AS '$[0]',
  JSON_EXTRACT(@json, '$[1]') AS '$[1]',
  JSON_EXTRACT(@json, '$[2]') AS '$[2]',
  JSON_EXTRACT(@json, '$[3]') AS '$[3]';

Output:

+------+------+------+------+
| $[0] | $[1] | $[2] | $[3] |
+------+------+------+------+
| 0    | 1    | 2    | 3    |
+------+------+------+------+

You can extract all the elements in an array with [*] as follows:

SET @json = '[0,1,2,3]';
SELECT JSON_EXTRACT(@json, '$[*]');

Output:

+-----------------------------+
| JSON_EXTRACT(@json, '$[*]') |
+-----------------------------+
| [0, 1, 2, 3]                |
+-----------------------------+

Example 2: Array in Object

With the help of path expressions, you can easily extract elements from an array in an object, as follows:

SET @json = '
  {
    "name": "Tom",
    "hobbies": [ "Football", "Piano" ]
  }
';

SELECT
  JSON_EXTRACT(@json, '$.hobbies[0]') AS "$.hobbies[0]",
  JSON_EXTRACT(@json, '$.hobbies[1]') AS "$.hobbies[1]";

Output:

+--------------+--------------+
| $.hobbies[0] | $.hobbies[1] |
+--------------+--------------+
| "Football"   | "Piano"      |
+--------------+--------------+

In this example, we use the path expression: $.hobbies[0], and $.hobbies locates the hobbies array in the JSON document first, and then [0] locates the first element in the hobbies array.

If the array you need to locate is in a large JSON document, its path may be very deep. You can use wildcards (**) for recursive matching:

SET @json = '{
  "a": {
    "name": "Tom",
    "hobbies": [ "Football", "Piano" ]
  }
}';

SELECT
  JSON_EXTRACT(@json, '$**.hobbies[0]') AS "hobbies[0]",
  JSON_EXTRACT(@json, '$**.hobbies[1]') AS "hobbies[1]";

Output:

+--------------+------------+
| hobbies[0]   | hobbies[1] |
+--------------+------------+
| ["Football"] | ["Piano"]  |
+--------------+------------+

In this example, we use the $**.hobbies[0] path expression to find all the first elements in all hobbies array in the JSON document.

Note: Since it is a recursive search, an array hobbies is returned.

Conclusion

This article gives several examples of extracting elements from JSON arrays with the help of MariaDB JSON_EXTRACT() functions.