How the JSON_CONTAINS_PATH() function works in Mariadb?

The JSON_CONTAINS_PATH() function is a JSON function that tests whether a JSON document contains any data at a given path or paths.

Posted on

The JSON_CONTAINS_PATH() function is a JSON function that 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.

Syntax

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

JSON_CONTAINS_PATH(json_doc, oneOrAll, path[, path] ...)

Where json_doc is a valid JSON document, oneOrAll is a string that can be either 'one' or 'all', and path is a valid JSON path expression.

Examples

Example 1: Testing whether a JSON document contains any of the given 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_PATH() function to test whether the JSON document contains any of the given paths. We use the 'one' argument to indicate that we want to check if any of the paths exist.

SELECT JSON_CONTAINS_PATH(JSON_OBJECT('name', 'Alice', 'age', 25, 'hobbies', 'reading'), 'one', '$.name', '$.gender', '$.hobbies') AS result;

The output is:

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

This means that the function returns 1 because the JSON document contains at least one of the given paths ($.name and $.hobbies).

Example 2: Testing whether a JSON document contains all of the given 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_PATH() function to test whether the JSON document contains all of the given paths. We use the 'all' argument to indicate that we want to check if all of the paths exist.

SELECT JSON_CONTAINS_PATH(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', 'sports'), 'all', '$.name', '$.age', '$.hobbies') AS result;

The output is:

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

This means that the function returns 1 because the JSON document contains all of the given paths.

Example 3: Testing whether a JSON document contains a nested path

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_PATH() function to test whether the JSON document contains a nested path. We use the 'one' argument to indicate that we want to check if any of the paths exist.

SELECT JSON_CONTAINS_PATH(JSON_OBJECT('name', 'Carol', 'age', 35, 'hobbies', JSON_ARRAY('music', 'movies', 'coding')), 'one', '$.hobbies[0]', '$.hobbies[2]') AS result;

The output is:

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

This means that the function returns 1 because the JSON document contains at least one of the nested paths ($.hobbies[0] and $.hobbies[2]).

Example 4: Getting NULL if the JSON document or any of the paths are invalid

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

SELECT JSON_CONTAINS_PATH('{"name": "Dave"', 'one', '$.name', '$.age') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

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

SELECT JSON_CONTAINS_PATH(JSON_OBJECT('name', 'Eve', 'age', 40), 'one', '$.name', '$**') AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the path $** is not valid (it contains a wildcard token that is not allowed in this situation).

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

  • JSON_CONTAINS(): This function 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.
  • 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_PATH() function is a useful JSON function that can test 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. There are also some other related functions that can test, extract, or search for values in JSON documents, such as JSON_CONTAINS(), JSON_EXTRACT(), and JSON_SEARCH().