MariaDB JSON_EXTRACT() Function

In MariaDB, JSON_EXTRACT() is a built-in function that extracts data from a JSON document given one or more paths.

MariaDB JSON_EXTRACT() Syntax

Here is the syntax for the MariaDB JSON_EXTRACT() function:

JSON_EXTRACT(json_doc, path, ...)

Parameters

json_doc

Required. A JSON document.

path

Required. You should specify at least one path expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_EXTRACT'.

Return value

The MariaDB JSON_EXTRACT() function returns all values ​​in the JSON document matched by the path expression. If the path expression matches a value, this function returns that value, and if the path expression matches multiple values, this function returns an array containing all values.

The JSON_EXTRACT() function will return NULL if:

  • The specified path does not exist in the JSON document.
  • Any parameter is NULL.

MariaDB JSON_EXTRACT() Examples

The following examples show the usage of the MariaDB JSON_EXTRACT() function.

Example: arrays

The following statement shows how to use the MariaDB JSON_EXTRACT() function to extract an element from a JSON array:

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]');

Output:

+------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2]') |
+------------------------------------------+
| {"x": 3}                                 |
+------------------------------------------+

Let’s look at another example with multiple path parameters:

SELECT JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]');

Output:

+------------------------------------------------------------+
| JSON_EXTRACT('[1, 2, {"x": 3}]', '$[2].x', '$[1]', '$[0]') |
+------------------------------------------------------------+
| [1, 2, 3]                                                  |
+------------------------------------------------------------+

Example: objects

The following statement shows how to use the MariaDB JSON_EXTRACT() function to extract the values of members of a JSON object:

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y');

Output:

+----------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y') |
+----------------------------------------------+
| [1, 2]                                       |
+----------------------------------------------+

Let’s look at another example with multiple path parameters:

SELECT JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y');

Output:

+-----------------------------------------------------+
| JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.x', '$.y') |
+-----------------------------------------------------+
| [1, [1, 2]]                                         |
+-----------------------------------------------------+

non-existent path

If none of the specified paths exists in the JSON document, the JSON_EXTRACT() function will return NULL:

SELECT JSON_EXTRACT('[1, 2]', '$[5]');

Output:

+--------------------------------+
| JSON_EXTRACT('[1, 2]', '$[5]') |
+--------------------------------+
| NULL                           |
+--------------------------------+

Conclusion

In MariaDB, JSON_EXTRACT() is a built-in function that extracts data from a JSON document given one or more paths.