How the JSON_EXISTS() function works in Mariadb?

The JSON_EXISTS() function is a JSON function that tests whether a JSON document contains data at a given path.

Posted on

The JSON_EXISTS() function is a JSON function that tests whether a JSON document contains data at a given path. The function takes a JSON document and a path as arguments and returns 1 or 0 depending on whether the path exists within the document or not. The function is similar to the JSON_CONTAINS_PATH() function, but it does not accept multiple paths or a oneOrAll argument.

Syntax

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

JSON_EXISTS(json_doc, path)

Where json_doc is a valid JSON document and path is a valid JSON path expression.

Examples

Example 1: Testing whether a JSON document contains data 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_EXISTS() function to test whether the JSON document contains data at the root level.

SELECT JSON_EXISTS(JSON_ARRAY(1, 2, 3), '$') AS result;

The output is:

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

This means that the function returns 1 because the JSON document contains data at the root level ($).

Example 2: Testing whether a JSON document contains data 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_EXISTS() function to test whether the JSON document contains data at the path $.name.

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

The output is:

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

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

Example 3: Testing whether a JSON document contains data at 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_EXISTS() function to test whether the JSON document contains data at the nested path $.hobbies[0].

SELECT JSON_EXISTS(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_ARRAY('sports', 'music', 'movies')), '$.hobbies[0]') AS result;

The output is:

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

This means that the function returns 1 because the JSON document contains data at the nested path $.hobbies[0].

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

In this example, we use the JSON_EXISTS() function to test whether a JSON document contains data at 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_EXISTS('"name": "Dave"', '$.name') 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_EXISTS(JSON_OBJECT('name', 'Eve', 'age', 40), '$**') 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).

Example 5: Getting 0 if the JSON document does not contain data at the given 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_EXISTS() function to test whether the JSON document contains data at the path $.gender.

SELECT JSON_EXISTS(JSON_OBJECT('name', 'Alice', 'age', 25), '$.gender') AS result;

The output is:

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

This means that the function returns 0 because the JSON document does not contain data at the path $.gender.

There are some other JSON functions that are related to the JSON_EXISTS() 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_EXISTS() function is a useful JSON function that can test whether a JSON document contains data at a given path. The function takes a JSON document and a path as arguments and returns 1 or 0 depending on whether the path exists within the document or not. The function is similar to the JSON_CONTAINS_PATH() function, but it does not accept multiple paths or a oneOrAll argument. There are also some other related functions that can test, extract, or search for data in JSON documents, such as JSON_CONTAINS_PATH(), JSON_EXTRACT(), and JSON_SEARCH().