BigQuery: Query with ARRAY_CONCAT() results in error

user_10819889 0
Viewed 6451

Suppose the following query (simplified from a more complex query):

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

Which results in the array [1,2,3,4,5,6]

Reformulating in the following query (with WITH statement) results in the error 'The argument to ARRAY_CONCAT (or ARRAY_CONCAT_AGG) must be an array type but was STRUCT, ARRAY, ARRAY> at [3:8]'

WITH q1 AS
(SELECT ([1, 2], [3, 4], [5, 6]) as count_to_six)
SELECT ARRAY_CONCAT(count_to_six) FROM q1

My question is: in the 'WITH AS'-query, how to get the correct query with the same results as in the initial query?

google-bigquery
2 Answers

Using parentheses around a comma-separated list creates a struct, so the WITH clause in your example is creating a struct with three array fields. You could do this instead:

WITH q1 AS (
  SELECT [1, 2] AS count_to_six UNION ALL
  SELECT [3, 4] UNION ALL
  SELECT [5, 6]
)
SELECT ARRAY_CONCAT_AGG(count_to_six)
FROM q1

This creates three rows of input in the WITH clause, each of which has an array with two elements, and then concatenates them into a single array. Note that the ordering of the arrays is not guaranteed, though, unless you somehow leverage the ORDER BY clause inside ARRAY_CONCAT_AGG.

If you really want to use ARRAY_CONCAT, you would need to name each field of the struct and then reference it inside the function call:

WITH q1 AS (
  SELECT STRUCT([1, 2] AS x, [3, 4] AS y, [5, 6] AS z) AS count_to_six
)
SELECT
  ARRAY_CONCAT(count_to_six.x, count_to_six.y, count_to_six.z)
FROM q1

The order of the elements in the resulting array is well-defined in this case.

user_62533470

Below is for BigQuery Standard SQL

#standardSQL 
WITH q1 AS (
  SELECT [1, 2] AS arr1, [3, 4] AS arr2, [5, 6] AS arr3
)
SELECT ARRAY_CONCAT(arr1, arr2, arr3) AS count_to_six
FROM q1

Another option - more generic, but little bit heavier than first one (but anyway you mentioned it will be a part of a more complex query)
I think it most resembles your original expectations 

#standardSQL 
WITH q1 AS (
  SELECT STRUCT([1, 2], [3, 4], [5, 6]) AS line 
)
SELECT 
  ARRAY(SELECT CAST(item AS INT64) FROM UNNEST(new_line) item) AS count_to_six 
FROM (
  SELECT 
    (SELECT ARRAY_CONCAT_AGG(SPLIT(arr)) 
      FROM UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(q1), r'\[(.+?)]')) arr
    ) new_line
  FROM q1
)
user_52219440
Related