BigQuery: Query with ARRAY_CONCAT() results in error

Question

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?

Elliott Brossard · Accepted Answer · 2019-01-11 15:20:31Z

6

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.

edited Jan 11, 2019 at 15:20

answered Jan 11, 2019 at 14:18

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Timo Rietveld Over a year ago

Thanks Elliott. Just to understand for me, would it also be possible to keep 'WITH q1 AS (SELECT ([1, 2], [3, 4], [5, 6]) as count_to_six)' as it is and convert the struct to an array in the SELECT statement outside WITH AS(), so that ARRAY_CONCAT() can be used with the desired result?

Elliott Brossard Over a year ago

I edited my answer to show that as well. You can't use the WITH clause as-is without imposing names on the struct fields somehow.

Timo Rietveld Over a year ago

All right. I just tried the query where you named each field of the struct, but this results in an error: Syntax error: Parenthesized expression cannot be parsed as an expression, struct constructor, or subquery at [2:11]

Elliott Brossard Over a year ago

You need to use the STRUCT keyword, actually, in order to use aliases.

Mikhail Berlyant · Accepted Answer · 2019-01-11 15:29:01Z

2

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
)

edited Jan 11, 2019 at 15:29

answered Jan 11, 2019 at 15:07

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

1 Comment

Timo Rietveld Over a year ago

Thanks Mikhail for the useful feedback, works as expected.

Collectives™ on Stack Overflow

BigQuery: Query with ARRAY_CONCAT() results in error

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related