In MySQL, the JSON_CONTAINS() function tests whether or not a specified value is found in the given JSON document or, optionally, at the specified path within the document.

If the value is found, JSON_CONTAINS() returns 1, otherwise it returns 0.

Syntax

The syntax goes like this:

JSON_CONTAINS(target, candidate[, path])

The function returns 1 or 0 depending on whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target.

Example 1 – Two Arguments

Here’s an example to demonstrate basic usage without specifying a path (i.e. providing two arguments only).

SET @doc1 = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @doc2 = '{"c": {"d": 3}}';
SELECT JSON_CONTAINS(@doc1, @doc2) AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Note the curly braces around the second argument. These are required in order to make it valid JSON. If we omit the curly braces, we’ll end up with an error:

SET @doc1 = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @doc2 = '"c": {"d": 3}';
SELECT JSON_CONTAINS(@doc1, @doc2) AS Result;

Result:

ERROR 3141 (22032): Invalid JSON text in argument 2 to function json_contains: "The document root must not be followed by other values." at position 3.

Example 2 – Three Arguments

In this example I add a third argument, to indicate a specific path for which to find the second argument in the first argument.

SET @doc1 = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @doc2 = '1';
SELECT JSON_CONTAINS(@doc1, @doc2, '$.a') AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

In this case, the second argument was found in the first argument at the path specified by the third argument.

If it wasn’t found, we’d end up with this:

SET @doc1 = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @doc2 = '2';
SELECT JSON_CONTAINS(@doc1, @doc2, '$.a') AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 3 – Dot Notation

You can use dot-notation to indicate specific paths within the JSON document.

SET @doc1 = '{"a": 1, "b": 2, "c": {"d": 3}}';
SET @doc2 = '3';
SELECT JSON_CONTAINS(@doc1, @doc2, '$.c.d') AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+