JSON_CONTAINS_PATH

Description

This function checks whether one or more specified paths exist in the target JSON document. It returns 1 if the paths exist in the JSON document, or 0 otherwise.

Syntax

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

Remarks

The json_doc parameter specifies the JSON document, and path specifies the path. If any parameter is NULL, this function returns NULL.

The one_or_all parameter can be set to the following values:

• 'one': This function returns 1 if at least one path exists in the document, or 0 otherwise.

• 'all': This function returns 1 if all paths exist in the document, or 0 otherwise.

The following conditions will cause an error:

• The json_doc parameter is not a valid JSON document.

• Any path parameter is not a valid path expression.

• The one_or_all parameter is not set to 'one' or 'all'.

Examples

SET @jn = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected (0.000 sec)

SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_CONTAINS_PATH(@jn, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.001 sec)

SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.001 sec)