JSON_CONTAINS_PATH()¶

JSON_CONTAINS_PATH(json, one_or_all, path…) tests whether the given JSON paths exist in a document, returning 1 when one or all of the paths are present depending on the mode.

Description¶

JSON_CONTAINS_PATH(json, one_or_all, path [, path] ...) returns 1 or 0 to indicate whether the named paths exist in the document:

With 'one', the function returns 1 if at least one of the paths exists.
With 'all', the function returns 1 if every path exists.

The mode string is case-insensitive. If the first argument is not valid JSON, an error is returned. A SQL NULL document or mode returns SQL NULL.

Paths are evaluated from left to right and evaluation short-circuits when the result is known. In 'one' mode, an earlier matching path returns 1 without evaluating later paths; in 'all' mode, an earlier missing path returns 0 without evaluating later paths. Therefore, a later SQL NULL path does not change a result already determined by an earlier path. If a SQL NULL path is evaluated before the result is determined, the function returns SQL NULL. An invalid JSON path raises an error when evaluation reaches it, but a later invalid path is skipped after an earlier path has already determined the result.

Syntax¶

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

Arguments¶

Argument	Description
`json`	The JSON document to test.
`one_or_all`	Either `'one'` or `'all'`, controlling whether any or all paths must exist.
`path`	One or more JSON path expressions to test.

Return Value¶

Returns 1 or 0 depending on whether the requested path or paths exist.

Examples¶

DROP DATABASE IF EXISTS json_contains_path_demo;
CREATE DATABASE json_contains_path_demo;
USE json_contains_path_demo;

SELECT JSON_CONTAINS_PATH('{"a":true,"b":[1,2]}', 'all', '$.a', '$.b') AS all_exist;
SELECT JSON_CONTAINS_PATH('{"a":true}', 'one', '$.a', '$.missing') AS one_exists;

CREATE TABLE t_json_contains_path (id INT, doc JSON);
INSERT INTO t_json_contains_path VALUES (1, '{"a":1,"b":[1,2]}'), (2, '{"a":null}');
SELECT id, JSON_CONTAINS_PATH(doc, 'all', '$.a', '$.b[1]') AS all_exist FROM t_json_contains_path ORDER BY id;

DROP DATABASE json_contains_path_demo;