Concatenate an array and two values into an array of unique values

Wasabi 0
Viewed 87

I have a table with three columns. Two of the columns (str1 and str2) are nullable strings and the other is a nullable array of strings (arr). I need to collect all of these into a single column arr while removing possible duplicate values (the original array column is guaranteed not to have duplicates).

The best I've been able to do is add one string column at a time, but I'd like to know if there's a better way:

WITH foo AS (
  SELECT 'A'  AS str1, 'B'  AS str2, ['C', 'D'] AS arr UNION ALL
  -- all get concatenated
  -- ['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' is not duplicated
  -- ['C', 'A', 'B'] and ['C', 'D', 'A']

  SELECT NULL AS str1, 'B'  AS str2, ['C', 'A'] AS arr UNION ALL
  -- NULL str1 (or str2) is ignored
  -- ['C', 'A', 'B']

  SELECT 'A'  AS str1, 'B'  AS str2, NULL       AS arr UNION ALL
  -- NULL array is ignored, str1 and 2 are array_concat'ed
  -- ['A', 'B']

  SELECT NULL AS str1, NULL AS str2, NULL       AS arr
  -- handles all NULLs
  -- NULL ([] or [''] would be ok)
)
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
)

In this implementation, if all the component values are NULL, arr will be NULL. However, an empty array or an array with a single '' would also be acceptable in this situation. Also, the order of items in the final array is irrelevant.

So, is there a cleaner, smarter way of doing this?

google-bigquery
2 Answers

Consider below query

SELECT ARRAY(SELECT DISTINCT str 
               FROM UNNEST([str1, str2] || IFNULL(arr, [])) str 
              WHERE str IS NOT NULL
       ) AS arr
  FROM foo;
Jaytiger0

Below is a little unorthodox approach, but still can be useful (at least from learning perspective)

select array(
    select distinct str
    from unnest(regexp_extract_all(to_json_string(t), r'"([^"]+)"')) str
    where not str in ('str1', 'str2', 'arr')
  ) arr
from foo t
Mikhail Berlyant0
Related