How the MEMBER OF() Operator Works in MySQL

In MySQL, the MEMBER OF() operator tests whether or not a value is a member of a given JSON array.

It returns true (1) if the array contains the value, and false (0) if it doesn’t.

The MEMBER OF() operator was added in MySQL 8.0.17.

Syntax

The syntax goes like this:

value MEMBER OF(json_array)

The operator tests whether or not value is a member of json_array.

Examples

Here’s an example to demonstrate:

SELECT 'Cat' MEMBER OF( '[ "Cat", "Dog", "Bird" ]' );

Result:

The result is 1 because the value of Cat exists in the array.

No Match

Here’s what happens when there’s no match:

SELECT 'Horse' MEMBER OF( '[ "Cat", "Dog", "Bird" ]' );

Result:

Partial Matches

Partial matches are considered a no match:

SELECT 'Ca' MEMBER OF( '[ "Cat", "Dog", "Bird" ]' );

Result:

Type Conversions

Type conversions of the operand are not performed:

SELECT 
    '2' MEMBER OF( '[ 1, 2, 3 ]' ) AS "String/Number",
    2 MEMBER OF( '[ 1, 2, 3 ]' ) AS "Number/Number";

Result:

+---------------+---------------+
| String/Number | Number/Number |
+---------------+---------------+
|             0 |             1 |
+---------------+---------------+

The first column returned 0 because I compared a string to a number. Even though the string contains the number 2, it is not a number and therefore it’s not considered a match.

When the Value is an Array

If the value itself is an array, we need to explicitly cast it as a JSON array:

SELECT 
    CAST('[ "Bird", "Fish" ]' AS JSON) 
    MEMBER OF( '[ "Cat", "Dog", [ "Bird", "Fish" ] ]' );

Result:

We can alternatively use the JSON_ARRAY() function:

SELECT 
    JSON_ARRAY('Bird', 'Fish') 
    MEMBER OF( '[ "Cat", "Dog", [ "Bird", "Fish" ] ]' );

Result:

Here’s what happens if we don’t cast it as a JSON array:

SELECT 
    '[ "Bird", "Fish" ]'
    MEMBER OF( '[ "Cat", "Dog", [ "Bird", "Fish" ] ]' );

Result:

No match.

When the Value is a JSON Object

Similarly, if the value is a JSON object, it must be coerced to the correct type.

We can do this with the CAST() function:

SELECT 
    CAST('{ "name" : "Wag" }' AS JSON)
    MEMBER OF( '[ { "name" : "Wag" }, { "name" : "Bark" } ]' );

Result:

We can also do it with the JSON_OBJECT() function:

SELECT 
    JSON_OBJECT('name', 'Wag')
    MEMBER OF( '[ { "name" : "Wag" }, { "name" : "Bark" } ]' );

Result:

Here’s what happens if we don’t convert the value to a JSON object:

SELECT 
    '{ "name" : "Wag" }'
    MEMBER OF( '[ { "name" : "Wag" }, { "name" : "Bark" } ]' );

Result: