BigQuery error ARRAY>>>

Question

I have some tables on BigQuery that the schema looks like below.

When I execute the below query I got some exceptions

select * from `test.test.test_partitioned` WHERE DATE(_PARTITIONTIME) = "2020-03-01"  and account.final_balance_sum >1

Cannot access field final_balance_sum on a value with type ARRAY<STRUCT<account_name STRING, final_balance_sum FLOAT64>> at [16:141]

I have tried the solution from here, seems it doesn't work to me at all.

Cannot access field in Big Query with type ARRAY<STRUCT<hitNumber INT64, time INT64, hour INT64, ...>>

can you share the schema of your table? seems like your table has nested fields. — Priya Agarwal, Jun 03 '20 at 12:08
Yes, it nested fields, I have attached my schemas on the ticket now — SharpLu, Jun 03 '20 at 12:10
I don't see final_balance_sum to be part of account record column. Did you try running - select * from `test.test.test_partitioned` WHERE DATE(_PARTITIONTIME) = "2020-03-01" and final_balance_sum >1 — Priya Agarwal, Jun 03 '20 at 12:37

score 2 · Accepted Answer · answered Jun 03 '20 at 14:00

2

select * EXCEPT(acc)
from `test.test.test_partitioned`,
UNNEST(account) acc
WHERE DATE(_PARTITIONTIME) = "2020-03-01"  
and acc.final_balance_sum >1

answered Jun 03 '20 at 14:00

Mikhail Berlyant

165,386
8
154
230

BigQuery error ARRAY>>>

1 Answers1