Back
Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query optimization, and usability that you won’t want to miss.
Watch Now

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, including 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('["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 |
+---------+

Questions?

Ask the Community, find solutions in the Troubleshooting Overview, get Self-Paced Training or learn more about our Support Plans.