MariaDB JSON_EXISTS() Function

In MariaDB, JSON_EXISTS() is a built-in function that checks whether a given path exists in a given JSON document.

The MariaDB JSON_EXISTS() function is similar to JSON_CONTAINS_PATH() functions .

You can also use the JSON_CONTAINS() function to check if a value exists in a JSON document.

MariaDB JSON_EXISTS() Syntax

Here is the syntax for the MariaDB JSON_EXISTS() function:

JSON_EXISTS(json_doc, path)

Parameters

json_doc

Required. A JSON document.

path

Required. The path to check.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_EXISTS'.

Return value

The MariaDB JSON_EXISTS() function returns 1 or 0 to indicate whether the path given by the path expression is contained in the given JSON document.

The MariaDB JSON_EXISTS() function will return NULL if the given JSON is invalid.

The MariaDB JSON_EXISTS() function will return NULL if any argument is NULL.

MariaDB JSON_EXISTS() Examples

The following examples show the usage of the MariaDB JSON_EXISTS() function.

Arrays

SET @json_doc = '[1, 2, {"x": 3}]';
SELECT
  @json_doc AS 'Json',
  JSON_EXISTS(@json_doc, '$[0]') AS `$[0]`,
  JSON_EXISTS(@json_doc, '$[3]') AS `$[3]`,
  JSON_EXISTS(@json_doc, '$[2].x') AS `$[2].x`;

Output:

+------------------+------+------+--------+
| Json             | $[0] | $[3] | $[2].x |
+------------------+------+------+--------+
| [1, 2, {"x": 3}] |    1 |    0 |      1 |
+------------------+------+------+--------+

In this example,

$[0]' represents the first element of the root array. $[3]' represents the 4th element of the root array. $[2].x represents the x member of the 3rd element.

SELECT JSON_EXISTS('{ "name": "Wag" }', '$.type');

Output:

+--------------------------------------------+
| JSON_EXISTS('{ "name": "Wag" }', '$.type') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+

Objects

SET @json_doc = '{"x":1, "y":[0,1]}';
SELECT
  @json_doc AS 'Json',
  JSON_EXISTS(@json_doc, '$.x') AS `$.x`,
  JSON_EXISTS(@json_doc, '$.y') AS `$.y`,
  JSON_EXISTS(@json_doc, '$.z') AS `$.z`,
  JSON_EXISTS(@json_doc, '$.y[0]') AS `$.y[0]`,
  JSON_EXISTS(@json_doc, '$.y[2]') AS `$.y[2]`;

Output:

+--------------------+------+------+------+--------+--------+
| Json               | $.x  | $.y  | $.z  | $.y[0] | $.y[2] |
+--------------------+------+------+------+--------+--------+
| {"x":1, "y":[0,1]} |    1 |    1 |    0 |      1 |      0 |
+--------------------+------+------+------+--------+--------+

Invalid JSON

The MariaDB JSON_EXISTS() function will return NULL if the given JSON is invalid.

SELECT JSON_EXISTS('a', '$[0]');

Output:

+--------------------------+
| JSON_EXISTS('a', '$[0]') |
+--------------------------+
|                     NULL |
+--------------------------+

NULL parameters

The MariaDB JSON_EXISTS() function will return NULL if any argument is NULL.

SELECT
    JSON_EXISTS(NULL, '$'),
    JSON_EXISTS('[1,2]', NULL);

Output:

+------------------------+----------------------------+
| JSON_EXISTS(NULL, '$') | JSON_EXISTS('[1,2]', NULL) |
+------------------------+----------------------------+
|                   NULL |                       NULL |
+------------------------+----------------------------+

Conclusion

In MariaDB, JSON_EXISTS() is a built-in function that checks whether a given path exists in a given JSON document.