MySQL MEMBER OF

Summary: in this tutorial, you will learn how to use the MySQL MEMBER OF operator that determines if a value is an element of a JSON array.

Introduction to MySQL MEMBER OF operator

The MEMBER OF operator returns true (1) if a value is an element of a JSON array or false (0) otherwise.

Here’s the syntax of the MEMBER OF operator:

value MEMBER OF (json_array)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• value: This is the value that can be a scalar or a JSON document.
• json_array: This is the JSON array of which you want to check if the value is an element.

The function returns NULL if the value or json_array is NULL.

MySQL MEMBER OF operator examples

Let’s take some examples of using the MEMBER OF operator.

1) Basic MEMBER OF operator examples

The following example uses the MEMBER OF operator to check if the number 1 is an element of a JSON array [1,2,3]:

SELECT 
  1 MEMBER OF('[1,2,3]') result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

The following example uses the MEMBER OF operator to check if the number 4 is an element of a JSON array [1,2,3]:

SELECT 
  4 MEMBER OF('[1,2,3]') result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

2) Conversion of value

The MEMBER OF operator does not carry a conversion when checking. For example:

SELECT 
  '1' MEMBER OF('[1,2,3]') result;Code language: SQL (Structured Query Language) (sql)

Since the string '1' is not in the array of numbers [1,2,3], the MEMBER OF operator returns 0:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Notice that the MEMBER OF operator does not carry a type conversion for the string.

In this case, you can use the CAST to convert the string '1' to the number 1 when checking like this:

SELECT 
  CAST('1' AS UNSIGNED) MEMBER OF('[1,2,3]') result;Code language: SQL (Structured Query Language) (sql)

Now the operator returns true because the number 1 is a member of the JSON array

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

3) Checking a JSON document

The following attempts to check if an object is a member of an array:

SELECT 
  '{"name": "John"}' MEMBER OF (
    '[{ "name" : "John" }, { "name" : "Joe" }]'
  ) result;Code language: SQL (Structured Query Language) (sql)

It returns false because the function treats the '{"name": "John"}' as a string, not a JSON object:

+--------+
| result |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

To do that, you need to convert the string to JSON before passing it to the MEMBER OF operator:

SELECT 
  CAST('{"name": "John"}' AS JSON) MEMBER OF (
    '[{ "name" : "John" }, { "name" : "Joe" }]'
  ) result;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

Summary

• Use the MEMBER OF operator that determines if a value is an element of a JSON array.