BigQuery多层嵌套字段UNNEST后仍无法访问子字段报错求助

问题分析 #

• 初始错误提示无法在类型为ARRAY<STRUCT<id STRING, totalResults INT64, resultType STRING, ...>>的值上访问字段criteria，本质是直接访问数组类型字段的子字段导致的——数组类型不能直接通过.点取子字段，必须先通过UNNEST展开。
• 执行SELECT name1.name2.name3.name4 FROM table报错：因为name1是ARRAY类型，未展开就尝试访问子字段name2。
• 执行SELECT n1.name2.name3.name4 FROM table, unnest(name1) as n1仍报错（针对name4）：说明n1.name2或n1.name2.name3仍是ARRAY类型，未逐层展开；或者name4字段名拼写错误/不存在于对应结构体中。

解决步骤 #

1. 确认表结构
   先执行语句查看完整嵌套结构，明确每一层是ARRAY还是STRUCT：

   DESCRIBE TABLE `your-project.your-dataset.your-table`;
   

2. 逐层UNNEST数组字段
   根据结构不同，对应不同的SQL写法：

   • 若只有name1是ARRAY，后续层级都是STRUCT：

     SELECT n1.name2.name3.name4
     FROM `your-project.your-dataset.your-table`
     , UNNEST(name1) AS n1
     

   • 若name1、name2、name3都是ARRAY：

     SELECT n3.name4
     FROM `your-project.your-dataset.your-table`
     , UNNEST(name1) AS n1
     , UNNEST(n1.name2) AS n2
     , UNNEST(n2.name3) AS n3
     

3. 检查字段名正确性
   确认name4的拼写完全匹配表结构中的字段名，避免因大小写、拼写错误导致的字段不存在报错。

内容的提问来源于stack exchange，提问作者Ferro