How to verify if a JSON value is contained in another a JSON (JavaScript Object Notation) value using the JSON_CONTAINS() function?

JSON_CONTAINS(target, candidate, path) is a MySQL built-in function that verifies if the given candidate JSON value is contained at the given location in the target JSON value.

JSON_CONTAINS() uses the following rules to calculate the return value:

• A scalar is contained in a scalar, if and only if they match each other.
• A scalar is contained in an array, if and only if the scalar is contained in any member of the array.
• A scalar is not contained in any object.
• An array is not contained in any scalar.
• An array is contained in an array, if and only if all members of the first array are contained in the second array.
• An array is not contained in any object.
• An object is not contained in any scalar.
• An object is contained in an array, if and only if the object is contained in any member of the array.
• An object is contained in an object, if and only if all keys of the first object exist in the second object, and their associated values are contained in corresponding values of the second object.

For example:

SET @j = '{"a":true, "b":[1,[2,[3,4]]], "c":{"x":6,"y":[7,[8,9]]}}';

SELECT JSON_CONTAINS(@j, 'true'), JSON_CONTAINS(@j, 'true', '$.a');
  -- +---------------------------+----------------------------------+
  -- | JSON_CONTAINS(@j, 'true') | JSON_CONTAINS(@j, 'true', '$.a') |
  -- +---------------------------+----------------------------------+
  -- |                         0 |                                1 |
  -- +---------------------------+----------------------------------+

SELECT JSON_CONTAINS(@j, '1', '$.b'), JSON_CONTAINS(@j, '7', '$.c');
  -- +-------------------------------+-------------------------------+
  -- | JSON_CONTAINS(@j, '1', '$.b') | JSON_CONTAINS(@j, '7', '$.c') |
  -- +-------------------------------+-------------------------------+
  -- |                             1 |                             0 |
  -- +-------------------------------+-------------------------------+

SELECT JSON_CONTAINS(@j, '[3,4]'), JSON_CONTAINS(@j, '[3,4]', '$.b[1][1]');
  -- +----------------------------+-----------------------------------------+
  -- | JSON_CONTAINS(@j, '[3,4]') | JSON_CONTAINS(@j, '[3,4]', '$.b[1][1]') |
  -- +----------------------------+-----------------------------------------+
  -- |                          0 |                                       1 |
  -- +----------------------------+-----------------------------------------+

SELECT JSON_CONTAINS(@j, '{"a":true}'), JSON_CONTAINS(@j, '{"a":false}');
  -- +---------------------------------+----------------------------------+
  -- | JSON_CONTAINS(@j, '{"a":true}') | JSON_CONTAINS(@j, '{"a":false}') |
  -- +---------------------------------+----------------------------------+
  -- |                               1 |                                0 |
  -- +---------------------------------+----------------------------------+

Note that rules described above treat nested arrays in a target JSON value as a single expanded array. For example:

SET @j = '{"a":true, "b":[1,[2,[3,4]]], "c":{"x":6,"y":[7,[8,9]]}}';

SELECT JSON_CONTAINS(@j, '[1,4]', '$.b');
  -- +-----------------------------------+
  -- | JSON_CONTAINS(@j, '[1,4]', '$.b') |
  -- +-----------------------------------+
  -- |                                 1 |
  -- +-----------------------------------+

SELECT JSON_CONTAINS(@j, '{"y":9}', '$.c');
  -- +-------------------------------------+
  -- | JSON_CONTAINS(@j, '{"y":9}', '$.c') |
  -- +-------------------------------------+
  -- |                                   1 |
  -- +-------------------------------------+

Reference information of the JSON_CONTAINS() function:

JSON_CONTAINS(target, candidate, path): int
  Verifies if the given candidate JSON value is contained at the given location
  in the target JSON value.

Arguments, return value and availability:
  target: Required. The JSON value to be searched in.
  candidate: Required. The JSON value to search for.
  path: Optional. The default is "$".
    The location of in the target to be searched in.
  int: Return value. The verification result.
  Available since MySQL 5.7.

⇒ JSON_CONTAINS_PATH() - Finding Path in JSON

⇐ JSON_ARRAY_INSERT() - Inserting Value to JSON Array

⇑ MySQL Functions on JSON Values

⇑⇑ MySQL Function References