I have the following table in Big Query which has an array of struct type. I have to perform a union operation with a simple table and want to add null values in place of the nested columns.

Actual nested table example -

Simple table (which needs to be union-ed)

I tried the following query but it gives an error of incompatible types on the nested columns

select * from actual_table 
union all
select acc, date, count,
array_agg(struct(cast(null as string) as device_id, cast(null as date) as to_date, cast(null as string) as from_date) as d
from simple_table

The resultant table should look like this -

CodePudding user response：

Since d has a type of array of struct<string, string, string>, you need to write a null struct like below.

SELECT * FROM actual_table
 UNION ALL
SELECT *, [STRUCT(CAST(null AS STRING), CAST(null AS STRING), CAST(null AS STRING))] FROM simple_table;

• [] is for array literal. see