JSON Member Conditions

JSON Member Condition is used to detect whether a value is a member of a JSON array.

Syntax

expr MEMBER [OF] ( json_expr )

The OF keyword is optional.

Usage instructions

• expr is the value to be checked.
• json_expr is a JSON array expression, which can be a JSON literal or a JSON path expression (such as column->'$.key').
• If expr is an element of the JSON array represented by json_expr, then TRUE is returned; otherwise, FALSE is returned.

Examples

Using a JSON literal:

obclient> SELECT 1 MEMBER OF('[1, 2, 3]') AS result;
+--------+
| result |
+--------+
|      1 |
+--------+
1 row in set

Use JSON path expressions:

obclient> CREATE TABLE t1 (profile JSON);
obclient> INSERT INTO t1 VALUES ('{"skills": ["python", "java", "go"]}');

obclient> SELECT * FROM t1
WHERE 'python' MEMBER OF (profile->'$.skills');