How the JSON_CONTAINS() function works in Mariadb?

The JSON_CONTAINS() function is a JSON function that tests whether a JSON document contains a specific value at a given path or paths.

Posted on

The JSON_CONTAINS() function is a JSON function that tests whether a JSON document contains a specific value at a given path or paths. The function takes a JSON document, a value, and an optional path as arguments. The function returns 1 or 0 depending on whether the value is found within the document at the specified path or paths. If the JSON document, the value, or any of the paths are invalid, the function returns NULL.

Syntax

The syntax of the JSON_CONTAINS() function is as follows:

JSON_CONTAINS(json_doc, val[, path])

Where json_doc is a valid JSON document, val is a JSON value, and path is a valid JSON path expression.

Examples

Example 1: Testing whether a JSON document contains a value at the root level

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_CONTAINS() function to test whether the JSON document contains the value 2 at the root level.

SELECT JSON_CONTAINS(JSON_ARRAY(1, 2, 3), 2) AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the JSON document contains the value 2 at the root level.

Example 2: Testing whether a JSON document contains a value at a specified path

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_CONTAINS() function to test whether the JSON document contains the value "Alice" at the path $.name.

SELECT JSON_CONTAINS(JSON_OBJECT('name', 'Alice', 'age', 25), '"Alice"', '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the JSON document contains the value "Alice" at the path $.name.

Example 3: Testing whether a JSON document contains a value at multiple paths

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_CONTAINS() function to test whether the JSON document contains the value "coding" at multiple paths: $.hobbies[0] and $.hobbies[2].

SELECT JSON_CONTAINS(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_ARRAY('coding', 'music', 'coding')), '"coding"', '$.hobbies[0]') AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the JSON document contains the value "coding" at both paths.

Example 4: Getting NULL if the JSON document or the value are invalid

In this example, we use the JSON_CONTAINS() function to test whether a JSON document contains a value at a given path. However, we use an invalid JSON document and an invalid value as arguments. The function returns NULL in this case.

SELECT JSON_CONTAINS('{"name": "Dave"', 1, '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns NULL because the JSON document is not valid (it is missing a closing brace).

SELECT JSON_CONTAINS(JSON_OBJECT('name', 'Eve', 'age', 40), 1, '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns NULL because the value is not valid (it is not a JSON value).

Example 5: Getting 0 if the JSON document does not contain the value at the given path or paths

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_CONTAINS() function to test whether the JSON document contains the value "Frank" at the path $.name.

SELECT JSON_CONTAINS(JSON_OBJECT('name', 'Alice', 'age', 25), '"Frank"', '$.name') AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns 0 because the JSON document does not contain the value "Frank" at the path $.name.

There are some other JSON functions that are related to the JSON_CONTAINS() function. Here are some of them:

  • JSON_CONTAINS_PATH(): This function tests whether a JSON document contains any data at a given path or paths. The function takes a JSON document, a oneOrAll argument, and one or more path arguments. The function returns 1 or 0 depending on whether any or all paths exist within the document. If the JSON document or any of the paths are invalid, the function returns NULL.
  • JSON_EXTRACT(): This function extracts a value or values from a JSON document at a given path or paths. The function takes a JSON document and one or more path arguments. The function returns a JSON value or values that match the path or paths. If the JSON document or any of the paths are invalid, the function returns NULL.
  • JSON_SEARCH(): This function searches for a value in a JSON document and returns the path or paths to the value. The function takes a JSON document, a oneOrAll argument, a search string, and an optional path as arguments. The function returns a JSON value or values that contain the path or paths to the value. If the JSON document, the search string, or any of the paths are invalid, the function returns NULL.

Conclusion

The JSON_CONTAINS() function is a useful JSON function that can test whether a JSON document contains a specific value at a given path or paths. The function takes a JSON document, a value, and an optional path as arguments. The function returns 1 or 0 depending on whether the value is found within the document at the specified path or paths. If the JSON document, the value, or any of the paths are invalid, the function returns NULL. There are also some other related functions that can test, extract, or search for values in JSON documents, such as JSON_CONTAINS_PATH(), JSON_EXTRACT(), and JSON_SEARCH().