How to use the MySQL JSON_CONTAINS

MySQL JSON_CONTAINS_PATH() is a function that tests whether a JSON document contains data at a given path or paths. It can be used to check if a JSON document has certain properties, elements, or subdocuments at specified locations. The function takes two or three arguments: the target JSON document, a keyword that indicates whether any or all paths must exist, and one or more path expressions. The return value is 1 if the condition is met, 0 if not, or NULL if any argument is NULL or invalid.

Syntax

The syntax of the function is:

JSON_CONTAINS_PATH(target, one_or_all, path[, 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.
one_or_all: A keyword that specifies whether the function returns 1 if any or all of the specified paths exist in the target document. It can be either ‘one’ or ‘all’. If it is ‘one’, the function returns 1 if the target document contains at least one of the paths, 0 otherwise. If it is ‘all’, the function returns 1 if the target document contains all of the paths, 0 otherwise.
path: One or more path expressions that indicate where to look for data 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.

Examples

Some examples of using the function are:

To check if a JSON document contains a specific property, use:
```
SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2, "c": 3}', 'one', '$.a');
```
The result is:
```
1
```
This means that the JSON document contains the property ‘a’.
To check if a JSON document contains a specific element in an array, use:
```
SELECT JSON_CONTAINS_PATH('{"colors": ["red", "green", "blue"]}', 'one', '$.colors[1]');
```
The result is:
```
1
```
This means that the JSON document contains the element ‘green’ in the array ‘colors’.
To check if a JSON document contains a specific subdocument, use:
```
SELECT JSON_CONTAINS_PATH('{"person": {"name": "Alice", "age": 25}}', 'one', '$.person');
```
The result is:
```
1
```
This means that the JSON document contains the subdocument ‘{“name”: “Alice”, “age”: 25}’.
To check if a JSON document contains all of the specified paths, use:
```
SELECT JSON_CONTAINS_PATH('{"person": {"name": "Alice", "age": 25}}', 'all', '$.person.name', '$.person.age');
```
The result is:
```
1
```
This means that the JSON document contains both the paths ‘$.person.name’ and ‘$.person.age’.
To check if a JSON document contains none of the specified paths, use:
```
SELECT JSON_CONTAINS_PATH('{"person": {"name": "Alice", "age": 25}}', 'all', '$.person.gender', '$.person.address');
```
The result is:
```
0
```
This means that the JSON document does not contain either of the paths ‘$.person.gender’ and ‘$.person.address’.

Similar Functions

Some similar functions to JSON_CONTAINS_PATH() are:

JSON_CONTAINS(): This function tests whether a JSON document contains a specific value, or a value at a specified path. It returns 1 or 0 depending on whether the value is found or not.
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.