JSON_ARRAY_CONTAINS_<type>
This is a set of three JSON functions, which return true if the given value is contained in the JSON array. JSON arrays are lists in this form:
["foo", "bar", "baz"] ["foo", 1, 2, 3]
JSON_ARRAY_CONTAINS_DOUBLE
is for all numeric data. Javascript has only one numeric type.JSON_ARRAY_CONTAINS_STRING
is for all text and binary types.JSON_ARRAY_CONTAINS_JSON
is for all valid Javascript types, includingtrue
,false
, JSON maps, and lists.
JSON_ARRAY_CONTAINS_<type>(json, value)
Arguments
json: a valid JSON array, or the name of a JSON column
value: the value to search
Return Value
1 (SQL TRUE) if the given JSON array contains a top-level element comparing equal to the specified value.
0 (SQL FALSE) if the specified value is not present, or if the specified value is SQL NULL.
SQL NULL if json is not a valid JSON array.
Examples
SELECT JSON_ARRAY_CONTAINS_STRING('["foo", "bar", "baz"]', 'foo') AS has_foo; +---------+ | has_foo | +---------+ | 1 | +---------+ SELECT JSON_ARRAY_CONTAINS_STRING('invalid', 'foo') AS has_foo; +---------+ | has_foo | +---------+ | NULL | +---------+ SELECT JSON_ARRAY_CONTAINS_DOUBLE('["foo", 1, 2, 3]', 3) AS has_three; +-----------+ | has_three | +-----------+ | 1 | +-----------+ SELECT JSON_ARRAY_CONTAINS_JSON('["foo", 1, true, 3]', 'true') AS has_truth; +-----------+ | has_truth | +-----------+ | 1 | +-----------+ SELECT JSON_ARRAY_CONTAINS_JSON('["foo", {"a":"b"}, 4]', '{"a":"b"}') AS has_map; +---------+ | has_map | +---------+ | 1 | +---------+