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:
1
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:
0
Partial Matches
Partial matches are considered a no match:
SELECT 'Ca' MEMBER OF( '[ "Cat", "Dog", "Bird" ]' );
Result:
0
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:
1
We can alternatively use the JSON_ARRAY()
function:
SELECT
JSON_ARRAY('Bird', 'Fish')
MEMBER OF( '[ "Cat", "Dog", [ "Bird", "Fish" ] ]' );
Result:
1
Here’s what happens if we don’t cast it as a JSON array:
SELECT
'[ "Bird", "Fish" ]'
MEMBER OF( '[ "Cat", "Dog", [ "Bird", "Fish" ] ]' );
Result:
0
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:
1
We can also do it with the JSON_OBJECT()
function:
SELECT
JSON_OBJECT('name', 'Wag')
MEMBER OF( '[ { "name" : "Wag" }, { "name" : "Bark" } ]' );
Result:
1
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:
0