BigQuery error ARRAY<STRUCT<gameType STRING, amount ARRAY<STRUCT<amount FLOAT64, type STRING>>>>

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
Yes, it nested fields, I have attached my schemas on the ticket now — SharpLu
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

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2020-06-03T14:00:59

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

BigQuery error ARRAY<STRUCT<gameType STRING, amount ARRAY<STRUCT<amount FLOAT64, type STRING>>>>

1 Answers