JSON_CONTAINS

Purpose

JSON_CONTAINS() verifies whether the specified JSON document is contained in the target JSON document. If a path argument is provided, the function also verifies whether the specified element is found at a specific path in the target document. If the specified JSON document is contained in the target JSON document, the function returns 1. Otherwise, it returns 0.

If you only need to check whether any data exists at the specified path, use JSON_CONTAINS_PATH().

Syntax

JSON_CONTAINS(target, candidate[, path])

Description

candidate specifies the JSON document to be verified, and target specifies the target JSON document.

If any argument is NULL, or path does not identify a part of the target document, the return value is NULL.

An error occurs in the following cases:

• The JSON document specified for candidate or target is invalid.

• The path expression specified for path is invalid.

• The specified path contains a * or ** wildcard.

The following rules apply to the function:

• Single elements can be equal only if two scalar values have the same JSON_TYPE() types and are comparable. Numeric values can be directly compared.

• A candidate array is contained in a target array only if all elements in the candidate array are contained in the target array.

• A candidate object is contained in a target object only if all keys in the candidate object are contained in the target object, and the value corresponding to each key is the same as that of the key in the target object.

Examples

obclient> SET @jn = '{"a": 1, "b": 2, "c": {"d": 4}}';
Query OK, 0 rows affected

obclient> SET @jn2 = '1';
Query OK, 0 rows affected

obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set

obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.b');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.b') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set

obclient> SET @jn2 = '{"d": 4}';
Query OK, 0 rows affected

obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.a');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.a') |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set

obclient> SELECT JSON_CONTAINS(@jn, @jn2, '$.c');
+---------------------------------+
| JSON_CONTAINS(@jn, @jn2, '$.c') |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set