In MariaDB, JSON_EXISTS() is a built-in function that allows you to check whether a value exists at a specified path in the JSON document.

It accepts the JSON document as an argument, and returns 1 if the path is found, and 0 if it’s not.

The MariaDB documentation states that the function “determines whether a specified JSON value exists in the given data”. However, the function doesn’t actually appear to check for a given value. It’s probably more accurate to say that it determines whether a specified path exists, or that a value exists at the specified path.

For checking that the actual value exists, you can use the JSON_CONTAINS() function.

Syntax

The syntax goes like this:

JSON_EXISTS(json_doc, path)

Where json_doc is the JSON document and path is the path to find.

Example

Here’s an example to demonstrate.

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

Result:

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

In this case, the path is found and the result is 1.

If the path isn’t found, the result is 0, like this:

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

Result:

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

Arrays

In this example, I check for the existence of an element at a given index of an array:

SELECT JSON_EXISTS(
    '{ 
        "name": "Wag", 
        "scores": [ 10, 8, 7 ] 
    }', 
    "$.scores[2]"
    ) AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Here’s what happens if the index doesn’t exist in the array:

SELECT JSON_EXISTS(
    '{ 
        "name": "Wag", 
        "scores": [ 10, 8, 7 ] 
    }', 
    '$.scores[3]'
    ) AS Result;

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Larger JSON Document

Here’s an example that uses a slightly larger JSON document. Here, I check for a key that exists within several levels of nesting:

SET @json_document = '
    { 
        "_id" : 1, 
        "name" : "Wag", 
        "details" : {
            "type" : "Dog", 
            "weight" : 20,
            "awards" : { 
                "Florida Dog Awards" : "Top Dog", 
                "New York Marathon" : "Fastest Dog", 
                "Sumo 2020" : "Biggest Dog"
            }
        }
    }
';
SELECT JSON_EXISTS(
    @json_document, 
    '$.details.awards.Sumo 2020'
    ) AS Result;

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

Null Arguments

If any of the arguments are NULL, the result is NULL:

SELECT 
    JSON_EXISTS(null, '$.a'),
    JSON_EXISTS('{ "a": 1 }', null);

Result:

+--------------------------+---------------------------------+
| JSON_EXISTS(null, '$.a') | JSON_EXISTS('{ "a": 1 }', null) |
+--------------------------+---------------------------------+
|                     NULL |                            NULL |
+--------------------------+---------------------------------+

Invalid JSON

Passing invalid JSON results in NULL:

SELECT JSON_EXISTS('{1}', '$.a');

Result:

+---------------------------+
| JSON_EXISTS('{1}', '$.a') |
+---------------------------+
|                      NULL |
+---------------------------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_EXISTS();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_EXISTS'

It’s the same when you provide too many arguments:

SELECT JSON_EXISTS('{"a": 1}', '$.a', 3);

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_EXISTS'