Create empty string array BigQuery

Question

I need to create an empty array, typed as < STRING > ARRAY

I need to append( UNION ) some real data, and some mocked data to reproduce not generated values.

My real data contains a struct of 2 string arrays. In my mocked data I tried to mock the empty array with the "[]" operator. But the union fails, because I cannot UNION a STRUCT of INT64 ARRAYS with a STRUCT of STRING ARRAYS.

Tried :

STRUCT([] AS ART,[] AS LIB)

Which succeed to generate a struct of empty INT64 ARRAYS. But leads to :

Error :

Column 4 in UNION ALL has incompatible types: STRUCT, LIB ARRAY>, STRUCT, LIB ARRAY> at [86:2]

user_6532822 · Answer

You can force a certain type like this:

WITH t AS (
  SELECT
    STRUCT(ARRAY[] AS ART,ARRAY[] AS LIB) as myStruct
  )

SELECT * FROM t

See https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical

Create empty string array BigQuery

1 Answers