Important
The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.
In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.
JSON_
On this page
The JSON_ function 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: