MariaDB JSON_CONTAINS(): Syntax, Paths and Examples

MariaDB JSON_CONTAINS() tests whether a candidate JSON value is contained in a target document, optionally at a specified path. It returns 1 when contained and 0 otherwise. See the official MariaDB JSON_CONTAINS reference.

If you need to check the existence of data at the path specified in the JSON document, use the JSON_CONTAINS_PATH() function .

MariaDB JSON_CONTAINS() Syntax

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

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)

Parameters

target_json

Required. A JSON document.

candidate_json

Required. The JSON value to look for in the target document.

path

Optional. A path expression.

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_CONTAINS'.

Return value

The function returns 1 if the candidate is contained in the target and 0 otherwise. If path is provided, it checks the value at that path. A valid path that does not match a value returns 0.

It returns NULL if any argument is NULL.

MariaDB raises an error if the target is not a valid JSON document, the path is invalid, or the path contains * or ** wildcards. To test whether a path exists, use JSON_CONTAINS_PATH().

MariaDB JSON_CONTAINS() Examples

Here are examples of the MariaDB JSON_CONTAINS() function.

Example: array

SET @json_doc = '[1, 2, {"x": 3}]';

SELECT
  @json_doc AS 'JSON',
  JSON_CONTAINS(@json_doc, '1') AS `1`,
  JSON_CONTAINS(@json_doc, '{"x": 3}') AS `{"x": 3}`,
  JSON_CONTAINS(@json_doc, '3') AS `3`;

Output:

+------------------+------+----------+------+
| JSON             | 1    | {"x": 3} | 3    |
+------------------+------+----------+------+
| [1, 2, {"x": 3}] |    1 |        1 |    0 |
+------------------+------+----------+------+

Example: specifying a path

SET @json_doc = '[1, 2, [3, 4]]';

SELECT
    JSON_CONTAINS(@json_doc, '2'),
    JSON_CONTAINS(@json_doc, '2', '$[2]');

Output:

+-------------------------------+---------------------------------------+
| JSON_CONTAINS(@json_doc, '2') | JSON_CONTAINS(@json_doc, '2', '$[2]') |
+-------------------------------+---------------------------------------+
|                             1 |                                     0 |
+-------------------------------+---------------------------------------+

Here, the path $[2] selects the nested array [3, 4]. That value does not contain the candidate 2, so the result is 0.

If the path is valid but does not select any value, the function returns 0:

SELECT JSON_CONTAINS('[1, 2]', '2', '$[5]');

0

Example: object

SET @json_doc = '{"x":1,"y":[1,2]}';

SELECT
  @json_doc AS 'JSON',
  JSON_CONTAINS(@json_doc, '{"x":1}') AS '{"x":1}',
  JSON_CONTAINS(@json_doc, '1', '$.y') AS '1 in $.y';

Output:

+-------------------+---------+----------+
| JSON              | {"x":1} | 1 in $.y |
+-------------------+---------+----------+
| {"x":1,"y":[1,2]} |       1 |        1 |
+-------------------+---------+----------+

NULL parameters

The MariaDB JSON_CONTAINS() function will return NULL if any argument is NULL:

SET @json_doc = '{"x":1,"y":[1,2]}';

SELECT
  JSON_CONTAINS(@json_doc, NULL),
  JSON_CONTAINS(NULL, NULL);

Output:

+--------------------------------+---------------------------+
| JSON_CONTAINS(@json_doc, NULL) | JSON_CONTAINS(NULL, NULL) |
+--------------------------------+---------------------------+
|                           NULL |                      NULL |
+--------------------------------+---------------------------+

Conclusion

In MariaDB, JSON_CONTAINS() is a built-in function that checks whether a JSON document contains another JSON document.