How to use the MySQL JSON_CONTAINS() function

MySQL JSON_CONTAINS() is a function that tests whether a given JSON document contains a specific value, or a value at a specified path. It can be used to check if a JSON document has a certain property, element, or subdocument. The function takes two or three arguments: the target JSON document, the candidate value, and an optional path expression. The return value is 1 if the value is found, 0 if not, or NULL if any argument is NULL or invalid.

Syntax

The syntax of the function is:

JSON_CONTAINS(target, candidate[, path])

The parameters are:

target: The JSON document to search in. It can be a JSON column, a JSON expression, or a string literal in a valid JSON format.
candidate: The value to look for in the target document. It can be a JSON column, a JSON expression, or a string literal in a valid JSON format.
path: An optional argument that specifies where to look for the candidate value in the target document. It can be a JSON path expression, or a string literal that begins with a dollar sign ($) and follows the rules of JSON path expressions. If omitted, the function searches the entire target document.

Examples

Some examples of using the function are:

To check if a JSON document contains a specific number, use:
```
SELECT JSON_CONTAINS('{"a": 1, "b": 2, "c": 3}', '2');
```
The result is:
```
1
```
This means that the JSON document contains the number 2.
To check if a JSON document contains a specific string, use:
```
SELECT JSON_CONTAINS('{"name": "Alice", "age": 25}', '"Alice"');
```
The result is:
```
1
```
This means that the JSON document contains the string “Alice”.
To check if a JSON document contains a specific array, use:
```
SELECT JSON_CONTAINS('{"colors": ["red", "green", "blue"]}', '["red", "blue"]');
```
The result is:
```
1
```
This means that the JSON document contains the array [“red”, “blue”].
To check if a JSON document contains a specific object, use:
```
SELECT JSON_CONTAINS('{"person": {"name": "Bob", "age": 30}}', '{"name": "Bob"}');
```
The result is:
```
1
```
This means that the JSON document contains the object {“name”: “Bob”}.
To check if a JSON document contains a specific value at a specific path, use:
```
SELECT JSON_CONTAINS('{"person": {"name": "Bob", "age": 30}}', '30', '$.person.age');
```
The result is:
```
1
```
This means that the JSON document contains the value 30 at the path $.person.age.

Similar Functions

Some similar functions to JSON_CONTAINS() are:

JSON_CONTAINS_PATH(): This function tests whether a JSON document contains data at a given path or paths. It returns 1 or 0 depending on whether any or all paths exist within the document.
JSON_EXTRACT(): This function extracts data from a JSON document using one or more path expressions. It returns the extracted data as a JSON value or NULL if no data is found.
JSON_SEARCH(): This function searches a JSON document for a string and returns the path to the matching element. It returns NULL if no match is found or if the document is not valid.