MariaDB JSON_CONTAINS_PATH(): Syntax, ONE vs ALL and Examples

MariaDB JSON_CONTAINS_PATH() checks whether one or more paths exist in a JSON document. It returns 1 or 0, or NULL if any argument is NULL. See the official MariaDB JSON_CONTAINS_PATH reference.

MariaDB `JSON_CONTAINS_PATH()` Syntax

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

JSON_CONTAINS_PATH(json_doc, return_arg, path[, path] ...)

Parameters

json_doc: Required. A JSON document.
return_arg: Required. Use 'one' to return 1 if at least one path exists; use 'all' to return 1 only if every path exists.
path: Required. Specify one or more JSONPath expressions.

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_CONTAINS_PATH'.

Return value

The MariaDB JSON_CONTAINS_PATH() function returns 1 if the requested path or paths exist, otherwise 0.

Whether JSON_CONTAINS_PATH() checks all paths depends on the return_arg parameter:

If 'one', the function returns 1 when at least one path exists; otherwise it returns 0.
If 'all', the function returns 1 only when every path exists; otherwise it returns 0.

If any argument is NULL, the function returns NULL.

An invalid JSON document or invalid JSONPath expression raises an error. Use JSON_VALID() to check a document separately.

MariaDB `JSON_CONTAINS_PATH()` Examples

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

Single path

To check whether a specified path exists in a JSON document, use the following statement:

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
    JSON_CONTAINS_PATH(@json_doc, 'all', '$[0]') as `$[0]`,
    JSON_CONTAINS_PATH(@json_doc, 'all', '$[3]') as `$[3]`,
    JSON_CONTAINS_PATH(@json_doc, 'all', '$[2].x') as `$[2].x`;

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

If there is only one parameter, the second parameter using 'one' or 'all' will get the same result, as follows:

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
    JSON_CONTAINS_PATH(@json_doc, 'one', '$[0]') as `$[0]`,
    JSON_CONTAINS_PATH(@json_doc, 'one', '$[3]') as `$[3]`,
    JSON_CONTAINS_PATH(@json_doc, 'one', '$[2].x') as `$[2].x`;

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

Example: `one` vs `all`

The example below shows what happens if you provide multiple paths;

SET @json_doc = '[1, 2, {"x": 3}]';

SELECT
    JSON_CONTAINS_PATH(@json_doc, 'one', '$[3]', '$[0]') as `one`,
    JSON_CONTAINS_PATH(@json_doc, 'all', '$[0]', '$[3]') as `all`;

+------+------+
| one  | all  |
+------+------+
|    1 |    0 |
+------+------+

In this example, the JSON document '[1, 2, {"x": 3}]' has a value at $[0] path, and does not have a value at '$[3]. The first function takes the one parameter, so it returns 1. The second function takes the all parameter, so it returns 0.

NULL arguments

The MariaDB JSON_CONTAINS_PATH() returns NULL if any parameter is NULL:

SELECT
  JSON_CONTAINS_PATH(NULL, 'one', '$.x') AS null_document,
  JSON_CONTAINS_PATH('{"x": 1}', NULL, '$.x') AS null_mode;

Output:

+---------------+-----------+
| null_document | null_mode |
+---------------+-----------+
| NULL          | NULL      |
+--------------------------------------+-----------------------------------+