MariaDB JSON_CONTAINS() Explained

In MariaDB, JSON_CONTAINS() is a built-in function that allows you to find out whether a specified value is found in the given JSON document, or at a specific path within the document.

It returns 1 if it does contain the value, 0 if it doesn’t, and NULL if any of the arguments are NULL.

Syntax

The syntax goes like this:

JSON_CONTAINS(json_doc, val[, path])

Where json_doc is the JSON document, val is the value to find, and path an optional value that specifies a path within the document.

Example

Here’s an example to demonstrate.

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

SELECT JSON_CONTAINS(@json_document, '{"name": "Wag"}');

Result:

+--------------------------------------------------+
| JSON_CONTAINS(@json_document, '{"name": "Wag"}') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+

In this case, there was a match, and the result is 1.

In the next example, there’s no match:

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

SELECT JSON_CONTAINS(@json_document, '{"name": "Woof"}');

Result:

+---------------------------------------------------+
| JSON_CONTAINS(@json_document, '{"name": "Woof"}') |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+

Note that the value is enclosed in curly braces.

Here’s what happens when the second argument is not valid:

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

SELECT JSON_CONTAINS(@json_document, 'Wag');

Result:

+--------------------------------------+
| JSON_CONTAINS(@json_document, 'Wag') |
+--------------------------------------+
|                                 NULL |
+--------------------------------------+
1 row in set, 1 warning (0.000 sec)

Let’s see the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 4038 | Syntax error in JSON text in argument 2 to function 'json_contains' at position 1 |
+---------+------+-----------------------------------------------------------------------------------+

Specify a Path

You can optionally use a third argument to specify a path.

Example:

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

SELECT JSON_CONTAINS(@json_document, 10, '$.weight');

Result:

+-----------------------------------------------+
| JSON_CONTAINS(@json_document, 10, '$.weight') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

When specifying a path, I didn’t need to use curly braces.

Here’s one searching for a string:

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

SELECT JSON_CONTAINS(@json_document, '"Wag"', '$.name');

Result:

+--------------------------------------------------+
| JSON_CONTAINS(@json_document, '"Wag"', '$.name') |
+--------------------------------------------------+
|                                                1 |
+--------------------------------------------------+

Notice I used double quotes inside the single quotes. If I omit the double quotes, here’s what happens:

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

SELECT JSON_CONTAINS(@json_document, 'Wag', '$.name');

Result:

+------------------------------------------------+
| JSON_CONTAINS(@json_document, 'Wag', '$.name') |
+------------------------------------------------+
|                                           NULL |
+------------------------------------------------+
1 row in set, 1 warning (0.000 sec)

And let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                           |
+---------+------+-----------------------------------------------------------------------------------+
| Warning | 4038 | Syntax error in JSON text in argument 2 to function 'json_contains' at position 1 |
+---------+------+-----------------------------------------------------------------------------------+

Nested Structures

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

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

SELECT JSON_CONTAINS(@json_document, 30, '$.specs.height');

Result:

+-----------------------------------------------------+
| JSON_CONTAINS(@json_document, 30, '$.specs.height') |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

Null Arguments

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

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

Result:

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

Incorrect Parameter Count

Providing no arguments results in an error:

SELECT JSON_CONTAINS();

Result:

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

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

SELECT JSON_CONTAINS('{ "a": 1}', 1, 2, 3);

Result:

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