Amazon Redshift数组存储及ARRAY_CONTAINS替代方案咨询

1. 类似 ARRAY_CONTAINS 的函数与替代方案 #

Redshift 没有原生的 ARRAY_CONTAINS 函数，但可以通过以下几种方式实现数组包含判断：

• 使用 JSON_PATH_EXISTS 函数：这是最直接高效的方式，通过JSON路径表达式检查元素是否存在。例如，假设JSON数组列名为 fruit_arr，存储内容为 ["apple", "banana", "orange"]，要检查是否包含"apple"：

  SELECT * FROM your_table
  WHERE JSON_PATH_EXISTS(fruit_arr, '$[*] ? (@ == "apple")')
  

  该方法无需展开数组，适合复杂JSON数组结构的场景。

• 展开数组后匹配：利用 UNNEST 配合 JSON_PARSE 将JSON数组拆分为行，再进行元素匹配：

  SELECT t.* FROM your_table t
  CROSS JOIN UNNEST(JSON_PARSE(t.fruit_arr)) AS arr_elements
  WHERE arr_elements = 'apple'
  

  这种方式适合需要同时处理数组元素的场景，但数据量大时可能会影响查询性能。

• 正则表达式匹配（简易场景）：如果是简单字符串数组，且元素不会出现包含目标值的情况，可以用 REGEXP_LIKE 快速判断：

  SELECT * FROM your_table
  WHERE REGEXP_LIKE(fruit_arr, '"apple"')
  

  注意：该方法存在误匹配风险（比如数组中有"applepie"时会误判），仅适合简单场景使用。

2. 存储JSON数组的最佳列类型 #

推荐两种列类型，根据集群版本和需求选择：

• SUPER 类型（优先推荐）：Redshift 新版本支持的半结构化数据类型，专门用于存储JSON、数组等非结构化数据。它会自动解析JSON格式，查询时无需额外转换，支持原生数组操作（比如直接通过 arr[0] 获取第一个元素），性能比VARCHAR存储更优。使用时直接插入合法的JSON字符串即可，Redshift会自动处理类型转换。

• VARCHAR 类型：兼容性最好的选项，适合所有Redshift版本。只要JSON数组长度不超过VARCHAR的最大限制（默认65535，可扩展至更大），就可以直接存储为字符串。查询时需要用 JSON_PARSE 将字符串转换为JSON对象后再操作，灵活性高但性能略逊于SUPER类型。

内容的提问来源于stack exchange，提问作者Igor