MariaDB JSON_CONTAINS() Function

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

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 document to be included.

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 JSON_CONTAINS() function returns 1 if the JSON document target_json contains the JSON document candidate_json, otherwise it returns 0. If provide the path parameter, checks whether the section matched by path contains a JSON document candidate_json.

The JSON_CONTAINS() function will return NULL if:

  • The specified path does not exist in the JSON document.
  • Any parameter is NULL.

The JSON_CONTAINS() function will return an error in the following cases:

  • MySQL will give an error if the argument json is not a valid JSON document. You can verify the validity of a JSON document using JSON_VALID().
  • If the path parameter is not a valid path expression, MariaDB will give an error.

MariaDB JSON_CONTAINS() Examples

Here are some common examples to show the usages 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, we have specified a path expression $[2]in JSON_CONTAINS('[1, 2, [3, 4]]', '2', '$[2]') which search 2 in [3, 4]. [3, 4] does not include 2 in, so this function returns 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.