MariaDB JSON_CONTAINS_PATH() Explained

In MariaDB, JSON_CONTAINS_PATH() is a built-in function that indicates whether a given JSON document contains data at the specified path or paths.

It returns 1 if the document does contain data at the specified path/s, 0 if it doesn’t, and NULL if any of the arguments are NULL.

Syntax

The syntax goes like this:

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

Where json_doc is the JSON document and path specifies the path for which to find data. Multiple paths can be provided.

The return_arg argument determines how to deal with multiple paths. It can be one or all.

• one – The function returns 1 if at least one path exists within the JSON document.
• all – The function returns 1 only if all paths exist within the JSON document.

Example

Here’s an example to demonstrate.

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(@json_document, 'one', '$.name');

Result:

+-----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_document, 'one', '$.name') |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

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

In the next example the path doesn’t exist, and the result is 0:

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(@json_document, 'one', '$.type');

Result:

+-----------------------------------------------------+
| JSON_CONTAINS_PATH(@json_document, 'one', '$.type') |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+

Multiple Paths

Here are some examples that search for multiple paths within the document:

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(
        @json_document, 
        'one', 
        '$.type', 
        '$.weight'
        ) AS Result;

Result:

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

In this example we searched for two paths. One path exists and the other doesn’t. But we got a 1 anyway (a positive result). This is because we used one as the second argument. The one specifies that we will get a 1 if any of the paths exist.

Here’s what happens if we use all as the second argument:

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(
    @json_document, 
    'all', 
    '$.type', 
    '$.weight'
    ) AS Result;

Result:

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

This time the result is 0, because not all paths exist in the JSON document.

If we change the missing path ($.type) to one that exists, we get a different result:

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(
    @json_document, 
    'all', 
    '$.name', 
    '$.weight'
    ) AS Result;

Result:

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

Arrays

Here’s an example that tests whether a given index exists within an array:

SET @json_document = '
    { 
        "name": "Wag", 
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';

SELECT JSON_CONTAINS_PATH(
        @json_document, 
        'one', 
        '$.awards[2]'
        ) AS Result;

Result:

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

And here’s what happens if we increase the index to a non-existent one:

SET @json_document = '
    { 
        "name": "Wag", 
        "awards" : [ "Top Dog", "Best Dog", "Biggest Dog" ] 
    }
';

SELECT JSON_CONTAINS_PATH(
        @json_document, 
        'one', 
        '$.awards[3]'
        ) AS Result;

Result:

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

Nested Structures

Here’s an example that looks for a path within a nested document:

SET @json_document = '
    { 
        "name": "Wag", 
        "specs": { 
            "weight": 10, 
            "height": 30 
            } 
    }
';

SELECT JSON_CONTAINS_PATH(
    @json_document, 
    'one', 
    '$.specs.height'
    ) AS Result;

Result:

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

Null Arguments

If any argument is NULL, the result is NULL:

SET @json_document = '{ "name": "Wag", "weight": 10 }';
SELECT 
    JSON_CONTAINS_PATH(null, 'one', '$.weight') AS a,
    JSON_CONTAINS_PATH(@json_document, null, '$.weight') AS b,
    JSON_CONTAINS_PATH(@json_document, 'one', null) AS c;

Result:

+------+------+------+
| a    | b    | c    |
+------+------+------+
| NULL | NULL | NULL |
+------+------+------+

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_CONTAINS_PATH();

Result:

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

The same thing happens when you don’t pass enough arguments:

SET @json_document = '{ "name": "Wag", "weight": 10 }';

SELECT JSON_CONTAINS_PATH(@json_document);

Result:

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