Purpose
You can call this function to verify 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] ...)
Notes
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