MariaDB JSON_EXTRACT(): Syntax, Paths and Examples

MariaDB JSON_EXTRACT() selects values from a JSON document using one or more JSONPath expressions. It returns matching values in path-match order and wraps results in an array when a path or path list can return multiple values. See the official MariaDB JSON_EXTRACT reference.

MariaDB `JSON_EXTRACT()` Syntax

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

JSON_EXTRACT(json_doc, path, ...)

Parameters

json_doc: Required. A valid JSON document. An invalid document causes an error.
path: Required. Specify at least one valid JSONPath expression. An invalid path causes an error.

MariaDB supports a subset of JSONPath: it uses lax mode and does not support strict mode or filter expressions. See the MariaDB JSONPath reference.

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 function returns the value matched by a single-result path. If a path or list of paths can return multiple values, MariaDB wraps the matches in a JSON array in matching order.

It returns NULL if no paths match or if any argument is NULL. An invalid JSON document or invalid path raises an error.

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]                                                  |
+------------------------------------------------------------+

Wildcard matches

An array wildcard can select the same member from each array element. Because this path can match several values, JSON_EXTRACT() returns an array:

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

[1, 2]

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