CODEWITHSUNDEEP
arraysgoogle-bigquery
Gwendal Yviquel
Gwendal Yviquel

Reputation: 392

Create empty string array BigQuery

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]

Upvotes: 3

Views: 5100

Answers (1)

Martin Weitzmann
Martin Weitzmann

Reputation: 4746

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

Upvotes: 7

Related Questions