MySQL JSON_CONTAINS() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_CONTAINS() function to check whether a JSON document contains another JSON document.

Introduction to MySQL JSON_CONTAINS() function

The JSON_CONTAINS() function checks whether a JSON document (target) contains another JSON document (candidate) at a path.

Here’s the syntax of the JSON_CONTAINS function:

JSON_CONTAINS(target, candidate[, path])Code language: SQL (Structured Query Language) (sql)

In this syntax:

• target: This is the target JSON document in which you want to check for the presence of the candidate JSON document.
• candidate: This is the candidate JSON document you want to check for within the target JSON document.
• path: This is the path within the target JSON document where you want to check for the candidate JSON document. The path argument is optional.

The JSON_CONTAINS() function returns 1 if the target JSON document contains the candidate JSON document or 0 otherwise.

The JSON_CONTAINS() function returns NULL if any argument is NULL or if the path argument does not identify a section of the target document.

The function issues an error in the following cases:

• The target or candidate is not a valid JSON document.
• The path argument is not a valid path expression.
• The path contains a * or ** wildcard.

MySQL JSON_CONTAINS() function examples

Let’s explore some examples to understand how the JSON_CONTAINS() function works.

1) Checking for Scalar Values

The following example uses the JSON_CONTAINS() function to check if the scalar value 1 is contained within a target JSON document at the path '$.a':

SELECT 
  JSON_CONTAINS(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    '1', '$.a'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The result is 1 because the candidate the scalar 1 is contained within the target JSON document at the specified path ‘$.a‘.

2) Checking for object elements

The following example uses the JSON_CONTAINS() function to check if a candidate JSON object {"d": 4} is contained within the target JSON document at the path $.c:

SELECT 
  JSON_CONTAINS(
    '{"a": 1, "b": 2, "c": {"d": 4}}', 
    '{"d": 4}', '$.c'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The function returns 1 because the candidate JSON object is contained within the target JSON document at the specified path $.c.

3) Checking for array elements

The following example uses the JSON_CONTAINS() function to check if the target JSON document contains target JSON array at the path $.numbers:

SELECT 
  JSON_CONTAINS(
    '{"numbers": [1, 2, 3, 4, 5], "fruits": ["apple", "banana", "cherry"]}', 
    '[1, 2, 3]', '$.numbers'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The function returns 1

The result is 1 because the JSON array [1, 2, 3] is contained within the target JSON document at the specified path $.numbers.

Summary

• Use the JSON_CONTAINS() function to check if a JSON document contains another JSON document at a specified path.