Redshift Spectrum查询嵌套JSON字段出现42703错误：列'my_nested_column'不存在的解决方法

我之前也碰到过完全一样的问题，折腾了好一阵才找到解决办法，给你几个实用的排查和修复方向：

• 检查Glue数据目录中字段的类型
  首先去Glue数据目录里查看my_nested_column的字段类型——有时候Glue Crawler会把嵌套JSON错误识别成string类型，而不是struct（或array<struct>）。你可以在Redshift里执行DESCRIBE my_external_schema.my_table;来确认类型：

  • 如果显示是string，说明Crawler没正确识别嵌套结构。这时候要么重新配置Crawler（确保S3里的JSON格式规范，没有混合类型），要么临时用Redshift的JSON函数提取字段：select c.id, json_extract_path_text(c.my_nested_column, 'MyField') from my_external_schema.my_table c;。当然最好的办法是让Crawler正确识别为struct，这样才能用点符号访问子字段。
  • 如果是struct或array<struct>类型，继续往下排查。

• 强制刷新Redshift Spectrum的元数据
  Glue数据目录更新后，Redshift可能会缓存旧的元数据，导致明明字段存在却报错。你可以在Redshift里执行这条命令强制刷新：

  ALTER EXTERNAL SCHEMA my_external_schema REFRESH METADATA;
  

  刷新完成后再重新执行你的查询，大概率能解决问题。

• 排查字段名的大小写问题
  JSON字段名的大小写可能会被Glue Crawler原样保留，但Redshift默认是大小写不敏感的——除非字段名在表结构里是用双引号包裹的。比如如果Glue里的字段名是"my_nested_column"（带引号），你在SQL里直接写my_nested_column就会提示不存在。这时候你需要给字段名加上双引号：

  select c.id , c."my_nested_column".MyField from my_external_schema.my_table c;
  

  你可以在Glue控制台查看表的DDL，确认字段名是否带引号。

• 验证S3中JSON数据的格式一致性
  如果S3里的部分JSON文件格式不规范（比如有的记录里my_nested_column是数组，有的是对象，或者部分记录缺失这个字段），Glue Crawler可能会生成错误的表结构。建议下载几个样本文件，用jq工具检查格式，确保所有记录里的my_nested_column都是统一的嵌套对象（或数组）类型。

• 如果是数组类型，使用正确的UNNEST语法
  要是my_nested_column是array<struct>类型，直接用点符号访问子字段会报错，必须先通过UNNEST展开数组。正确的SQL写法应该是：

  select c.id, nested.MyField
  from my_external_schema.my_table c
  cross join unnest(c.my_nested_column) as t(nested);
  

先从刷新元数据和检查字段类型这两个方向入手，这是最常见的问题根源，应该能快速解决你的报错。

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