如何更简洁地将可空字符串列与可空字符串数组合并为无重复值的数组

我有一个包含三列的表：其中两列（str1和str2）是可空字符串，另一列是可空字符串数组（arr）。我需要将这些列的所有值整合到一个名为arr的单列中，同时去除可能存在的重复值（原数组列arr保证无重复值）。目前我能做到的最佳方式是逐个添加字符串列，但想了解是否有更优的实现方法。

现有实现代码如下：

WITH foo AS (
 SELECT 'A' AS str1, 'B' AS str2, ['C', 'D'] AS arr UNION ALL -- 所有值将被合并 -- ['C', 'D', 'A', 'B']
 SELECT 'A' AS str1, 'B' AS str2, ['C', 'A'] AS arr UNION ALL
 SELECT 'A' AS str1, 'A' AS str2, ['C', 'D'] AS arr UNION ALL -- 'A'不会重复 -- ['C', 'A', 'B'] 和 ['C', 'D', 'A']
 SELECT NULL AS str1, 'B' AS str2, ['C', 'A'] AS arr UNION ALL -- 忽略NULL的str1（或str2） -- ['C', 'A', 'B']
 SELECT 'A' AS str1, 'B' AS str2, NULL AS arr UNION ALL -- 忽略NULL数组，将str1和str2合并 -- ['A', 'B']
 SELECT NULL AS str1, NULL AS str2, NULL AS arr -- 处理全NULL情况 -- NULL（空数组[]或['']也可接受）
)
SELECT
 CASE
 WHEN str2 IS NULL OR str2 IN UNNEST(arr) THEN arr
 WHEN arr IS NULL THEN [str2]
 ELSE ARRAY_CONCAT(arr, [str2])
 END AS arr
FROM (
 SELECT
 CASE
 WHEN str1 IS NULL OR str1 IN UNNEST(arr) THEN arr
 WHEN arr IS NULL THEN [str1]
 ELSE ARRAY_CONCAT(arr, [str1])
 END AS arr, str2
 FROM foo
)

在当前实现中，若所有组件值均为NULL，结果arr会是NULL，但在此场景下，空数组或包含单个''的数组也是可接受的。另外，最终数组中元素的顺序无关紧要。请问是否存在更简洁、更巧妙的实现方式？

针对你的需求，这里有个更直观简洁的实现思路：把所有非空的元素（原数组的元素、str1、str2）统一收集到一个临时集合中，再去重后重新聚合为数组，同时灵活处理全NULL的场景。

实现代码 #

WITH foo AS (
 SELECT 'A' AS str1, 'B' AS str2, ['C', 'D'] AS arr UNION ALL
 SELECT 'A' AS str1, 'B' AS str2, ['C', 'A'] AS arr UNION ALL
 SELECT 'A' AS str1, 'A' AS str2, ['C', 'D'] AS arr UNION ALL
 SELECT NULL AS str1, 'B' AS str2, ['C', 'A'] AS arr UNION ALL
 SELECT 'A' AS str1, 'B' AS str2, NULL AS arr UNION ALL
 SELECT NULL AS str1, NULL AS str2, NULL AS arr
)
SELECT
  CASE
    -- 处理全NULL场景，可按需替换为 [] 或 ['']
    WHEN COALESCE(str1, str2, arr) IS NULL THEN NULL
    ELSE ARRAY(
      SELECT DISTINCT value
      FROM UNNEST(ARRAY_CONCAT(
        IFNULL(arr, []),       -- 原数组为NULL则转为空数组
        IFNULL([str1], []),    -- str1为NULL则转为空数组
        IFNULL([str2], [])     -- str2为NULL则转为空数组
      )) AS value
      WHERE value IS NOT NULL  -- 过滤掉所有NULL值
    )
  END AS arr
FROM foo;

方案优势 #

1. 逻辑直观易读：不需要嵌套多层CASE WHEN，直接通过ARRAY_CONCAT把所有可能的非空元素合并，再通过SELECT DISTINCT去重，最后聚合为数组。
2. 灵活处理NULL：用IFNULL把单个字符串或原数组转为空数组，避免ARRAY_CONCAT出现NULL；WHERE value IS NOT NULL确保最终数组里没有NULL元素。
3. 全场景覆盖：通过COALESCE(str1, str2, arr)判断是否全为NULL，可根据需求轻松修改返回值（比如替换为[]得到空数组，或['']得到包含空字符串的数组）。

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