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.