The MEMBER OF function in MySQL is used to determine whether a specified value is an element of a JSON array. It takes two parameters: value and json_array. The value parameter can be any valid JSON value, and the json_array parameter must be a valid JSON array.

Syntax

Here is the syntax for the MEMBER OF function:

MEMBER OF(json_array, value)

where:

json_array is the JSON array to be checked.
value is the value to be checked for membership.

The MEMBER OF() function returns 1 if the specified value is an element of the JSON array, and 0 if it is not.

Example

For example, the following query checks whether the value “apple” is an element of the JSON array [“apple”, “orange”, “banana”]:

SELECT 'apple' MEMBER OF('["apple", "orange", "banana"]');

This query will return 1, indicating that the value “apple” is indeed an element of the JSON array.

The MEMBER OF() function also supports the use of path expressions to check for elements at specific locations within a JSON document. For example, the following query checks whether the value “orange” is an element of the JSON object {“fruits”: [“apple”, “orange”, “banana”]} at the “fruits” path:

SELECT "orange" MEMBER OF(JSON_EXTRACT('{"fruits": ["apple", "orange", "banana"]}', '$.fruits[1]'));

This query will also return 1, indicating that the value “orange” is an element of the JSON object at the specified path.

Here is a table summarizing the behavior of the MEMBER OF() function:

value: The value to check for.
json_array: The JSON array to search.
Return value: 1 if the value is an element of the array, 0 otherwise.

The MEMBER OF() function is a valuable tool for working with JSON data in MySQL. It can be used to verify whether specific values are present in JSON arrays or objects, which can be helpful for data validation and manipulation tasks.