JSON_CONTAINS()¶

JSON_CONTAINS(target, candidate, path) returns 1 when the candidate JSON value is contained within the target document at the specified path, and 0 otherwise.

Description¶

JSON_CONTAINS(target, candidate) tests whether candidate is contained within target. An array is contained in another array if every element of the candidate appears in the target. An object is contained in another object if every key-value pair of the candidate appears in the target.

When an optional path argument is provided, containment is checked only for the value found at that path. Paths are evaluated with the same semantics as other MySQL JSON functions; a path that does not exist returns SQL NULL.

Numeric values are compared across integer, decimal, and floating-point representations when they are equal; non-numeric scalar types must match exactly.

Syntax¶

JSON_CONTAINS(target, candidate [, path])

Arguments¶

Argument	Description
`target`	The JSON document (or JSON-compatible string) to search within.
`candidate`	The JSON document to look for.
`path`	Optional. A JSON path expression restricting where containment is checked.

Return Value¶

Returns 1 if candidate is contained in target, and 0 otherwise. Returns SQL NULL if any argument is SQL NULL or if the optional path does not exist. An invalid JSON document or JSON path raises an error.

Examples¶

DROP DATABASE IF EXISTS json_contains_demo;
CREATE DATABASE json_contains_demo;
USE json_contains_demo;

SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": {"d": 4}}', '1', '$.a') AS has_a;
SELECT JSON_CONTAINS('[1,2,3]', '[1,3]') AS contains_1_and_3;
SELECT JSON_CONTAINS('{"tags":["x","y"]}', '"x"', '$.tags') AS has_x;

CREATE TABLE t_json_contains (id INT, doc VARCHAR(200));
INSERT INTO t_json_contains VALUES
    (1, '{"type":"doc","tags":["x","y"]}'),
    (2, '{"type":"page","tags":["z"]}');
SELECT id, JSON_CONTAINS(doc, '"doc"', '$.type') AS is_doc FROM t_json_contains ORDER BY id;

DROP DATABASE json_contains_demo;