MariaDB JSON_CONTAINS_PATH() Function

In MariaDB, JSON_CONTAINS_PATH() is a built-in function that checks whether a value exists at a given path in a JSON document.

Returns if the document does contain the data at the specified path, if not, and if any parameters are.1``0``NULL``NULL

MariaDB `JSON_CONTAINS_PATH()` Syntax

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

JSON_CONTAINS_PATH(json, one_or_all, path[, path])

Parameters

json: Required. A JSON document.
one_or_all: Required. Available values: 'one', 'all'. It indicates whether to check all paths.
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_CONTAINS_PATH'.

Return value

The MariaDB JSON_CONTAINS_PATH() function will return 1 if the JSON document has a value at the specified path, otherwise 0.

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

If 'one', and there is a value on at least one path, the JSON_CONTAINS_PATH() function returns 1, otherwise 0.
If 'all', and all paths have values, the JSON_CONTAINS_PATH() function will return 1, otherwise return 0.

If either argument is NULL, the JSON_CONTAINS_PATH() function will return NULL.

The JSON_CONTAINS_PATH() function will return an error in the following cases:

MySQL will give an error if the argument json is not a valid JSON document. You can verify the validity of a JSON document using JSON_VALID().
If the path parameter is not a valid path expression, MySQL will give an error.

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 parameter

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

SELECT
  JSON_CONTAINS_PATH(null, 'one', '$'),
  JSON_CONTAINS_PATH('', null, '$');

Output:

+--------------------------------------+-----------------------------------+
| JSON_CONTAINS_PATH(null, 'one', '$') | JSON_CONTAINS_PATH('', null, '$') |
+--------------------------------------+-----------------------------------+
|                                 NULL |                              NULL |
+--------------------------------------+-----------------------------------+

Conclusion

In MariaDB, JSON_CONTAINS_PATH() is a built-in function that checks whether a value exists at a given path in a JSON document.

MariaDB JSON_CONTAINS_PATH() Syntax