JSON_
On this page
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:
["alpha", "beta", "gamma"]["alpha", 1, 2, 3]
-
JSON_is for all numeric data.ARRAY_ CONTAINS_ DOUBLE Javascript has only one numeric type. -
JSON_is for all text and binary types.ARRAY_ CONTAINS_ STRING -
JSON_is for all valid Javascript types, includingARRAY_ CONTAINS_ JSON true,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('["alpha", "beta", "gamma", "delta"]', 'delta') AS has_delta;
+-----------+
| has_delta |
+-----------+
| 1 |
+-----------+SELECT JSON_ARRAY_CONTAINS_STRING('beta', 'delta') AS has_alpha;
+-----------+
| has_alpha |
+-----------+
| 0 |
+-----------+SELECT JSON_ARRAY_CONTAINS_STRING('1', 'delta') AS has_delta;
+-----------+
| has_delta |
+-----------+
| NULL |
+-----------+SELECT JSON_ARRAY_CONTAINS_DOUBLE('["delta", 1, 2, 3]', 2) AS has_three;
+-----------+
| has_three |
+-----------+
| 1 |
+-----------+SELECT JSON_ARRAY_CONTAINS_JSON('["delta", 1, true, 3]', 'true') AS is_truth;
+----------+
| is_truth |
+----------+
| 1 |
+----------+SELECT JSON_ARRAY_CONTAINS_JSON('["letter", {"alpha":"beta"}, 4]', '{"alpha":"beta"}') AS has_map;
+---------+
| has_map |
+---------+
| 1 |
+---------+Last modified: March 7, 2023