MySQL JSON_CONTAINS() Function

In MySQL, the JSON_CONTAINS() function checks whether one JSON document contains another JSON document.

If you need to check for the existence of a path in a JSON document, use the JSON_CONTAINS_PATH() function.

`JSON_CONTAINS()` Syntax

Here is the syntax of the MySQL JSON_CONTAINS() function:

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

Parameters

target_json: Required. A JSON document.
candidate_json: Required. The included JSON document.
path: Optional. The path expression.

Return value

The function will return 1 if the JSON document target_json includes the JSON document candidate_json, otherwise returns 0. If the parameter path is provided, JSON_CONTAINS() checks if the section specified by the path includes candidate_json.

The JSON_CONTAINS() function will return NULL in the following cases:

The specified path does not exist in the JSON document.
Any parameter is NULL.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.

`JSON_CONTAINS()` Examples

Here are some examples of JSON_CONTAINS().

Example: Array

SELECT
    JSON_CONTAINS('[1, 2, {"x": 3}]', '1') as `1`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '{"x": 3}') as `{"x": 3}`,
    JSON_CONTAINS('[1, 2, {"x": 3}]', '3') as `3`;

+------+----------+------+
| 1    | {"x": 3} | 3    |
+------+----------+------+
|    1 |        1 |    0 |
+------+----------+------+

Since 3 is not an element of the array [1, 2, {"x": 3}], so JSON_CONTAINS('[1, 2, {"x": 3}]', '3') returns 0.

Example: Using A Path

SELECT
    JSON_CONTAINS('[1, 2, [3, 4]]', '2'),
    JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]');

+--------------------------------------+----------------------------------------------+
| JSON_CONTAINS('[1, 2, [3, 4]]', '2') | JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') |
+--------------------------------------+----------------------------------------------+
|                                    1 |                                            0 |
+--------------------------------------+----------------------------------------------+

Here, we’ve specified the path expression $[2] in JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]'), which matches [3, 4]. Since [3, 4] does not includes 2, so it returns 0.

JSON_CONTAINS() Syntax