google-bigqueryunnest

Cannot access field criteria on a value with type ARRAY<STRUCT<id STRING, totalResults INT64, resultType STRING, ...>>

I have multiple levels in the Bigquery column names, and after using UNNEST, I'm still getting the error shown in the title.

SELECT name1.name2.name3.name4 FROM table -- error

SELECT n1.name2.name3.name4 FROM table, unnest(name1) as n1 --still error, this time for name4

Solution

  • It works after using multiple unnests; one for every name that gives the error:

    SELECT n.name4 FROM table, unnest(name1) as n1, unnest(n1.name2.name3) as n