MySQL JSON_CONTAINS_PATH() Function

In MySQL, the JSON_CONTAINS_PATH() function checks whether a JSON document has a specified path.

JSON_CONTAINS_PATH() Syntax

Here is the syntax of the MySQL 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.

Return value

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

JSON_CONTAINS_PATH() check all paths according to the one_or_all parameter :

• If it is 'one', and at least one path has data, the JSON_CONTAINS_PATH() function will return 1, otherwise it will return 0.
• If it is 'all', and all paths have data, the JSON_CONTAINS_PATH() function will return 1, otherwise it will return 0.

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

There will happen an error in the following cases:

• MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
• If the parameter path is not a valid path expression, MySQL will give an error.

JSON_CONTAINS_PATH() Examples

Here are some examples of JSON_CONTAINS().

Example: Array

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

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

Since the array [1, 2, {"x": 3}] has 3 elements, and there is no data in $[3], so JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[3]') returns 0.

Example: one vs all

SELECT
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'one', '$[0]', '$[3]') as `one`,
    JSON_CONTAINS_PATH('[1, 2, {"x": 3}]', 'all', '$[0]', '$[3]') as `all`;

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

Since the array has no data in $[3]，so it returns 1 with the parameter one and returns 0 with the parameter all.