Array Contains in Amazon Redshift

userw9Sl1 0
Viewed 53

We need to store an array in a Redshift table. But Redshift doesn't support Array type, so it could be JSON array. Also, we need to be able to use this array in SQL queries

  1. Is exist some function like ARRAY_CONTAINS in Redshift? Maybe it exists some workaround for it
  2. What is the best column type to store arrays or JSON Array in Redshift?

Thanks

amazon-redshift arrays
1 Answers

Redshift can store array data as a SUPER datatype. You can find into on using this datatype here - https://docs.aws.amazon.com/redshift/latest/dg/super-overview.html

I don't believe there is an "array_contains" equivalent and a full list of SUPER functions can be found here - https://docs.aws.amazon.com/redshift/latest/dg/c_Type_Info_Functions.html

As a work-around I'd suggest converting the array to a string using json_serialize() and look for the element in the resulting string (with enclosing commas or square brackets to ensure a full match).

Bill Weiner0
Related