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<ART ARRAY<STRING>, LIB ARRAY<STRING>>, STRUCT<ART ARRAY<INT64>, LIB ARRAY<INT64>> at [86:2]

Martin Weitzmann · Accepted Answer · 2019-10-22 12:32:21Z

7

You can force a certain type like this:

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

SELECT * FROM t

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

answered Oct 22, 2019 at 12:32

Martin Weitzmann

4,75612 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Create empty string array BigQuery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related