Purpose
JSON_CONTAINS_PATH() verifies whether one or more specified paths are contained in the specified JSON document. If yes, the function returns 1. Otherwise, it returns 0.
Syntax
JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
Description
json_doc specifies the name of the JSON document, and path specifies the path. If any argument is NULL, the return value is NULL.
You can set one_or_all to one of the following values:
'one': If at least one path exists in the document, the function returns 1. Otherwise, it returns 0.'all': If all paths exist in the document, the function returns 1. Otherwise, it returns 0.
An error occurs in the following cases:
The JSON document specified for
json_docis invalid.Any path expression specified for
pathis invalid.one_or_allis set to a value other than'one'or'all'.
Examples
obclient> SET @jn = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected
obclient> SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_CONTAINS_PATH(@jn, 'all', '$.a', '$.e');
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
| 0 |
+---------------------------------------------+
1 row in set
obclient> SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
| 1 |
+----------------------------------------+
1 row in set
obclient> SELECT JSON_CONTAINS_PATH(@jn, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
| 0 |
+----------------------------------------+
1 row in set
