MySQL MEMBER OF() Function

In MySQL, the MEMBER OF() function checks whether a specified value is an element of a JSON array.

`MEMBER OF()` Syntax

Here is the syntax of the MySQL MEMBER OF() function:

value MEMBER OF(json_array)

Parameters

value: Required. The value to check. It can be of any type.
json_array: Required. The JSON array.

Return value

If json_array contains value, MEMBER OF() returns 1, otherwise returns 0.

The MEMBER OF() function returns 1 if both value and json_array are scalar and equal.

MySQL will give an error if the parameter json_array is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.

`MEMBER OF()` Examples

Basic usage

SELECT
    1 MEMBER OF('[1, 2, "a"]'),
    'a' MEMBER OF('[1, 2, "a"]');

+----------------------------+------------------------------+
| 1 MEMBER OF('[1, 2, "a"]') | 'a' MEMBER OF('[1, 2, "a"]') |
+----------------------------+------------------------------+
|                          1 |                            1 |
+----------------------------+------------------------------+

scalars

The MEMBER OF() function returns 1 if both value and json_array are scalar and equal.

SELECT
    1 MEMBER OF('1'),
    'a' MEMBER OF('"a"'),
    CAST('true' AS JSON) MEMBER OF('true');

+------------------+----------------------+----------------------------------------+
| 1 MEMBER OF('1') | 'a' MEMBER OF('"a"') | CAST('true' AS JSON) MEMBER OF('true') |
+------------------+----------------------+----------------------------------------+
|                1 |                    1 |                                      1 |
+------------------+----------------------+----------------------------------------+

Here, we used the CAST() function to convert 'true' from a string to a JSON value.

MEMBER OF() Syntax