如何在Snowflake中从嵌套JSON提取agent_first_name字段？

错误原因 #

你最后一次执行的LATERAL FLATTEN(INPUT => ev.value)会将evaluations数组中的对象（包含channel_meta的对象）拆解为键值对结构，此时cm的返回结果是类似{"key": "channel_meta", "value": {...}}的键值对，而非包含channel_meta字段的对象，因此cm.channel_meta会被识别为无效标识符。

正确解法（推荐） #

只需要展开JSON中的数组层级（evaluation_forms和evaluations都是数组），无需展开对象层级，直接通过路径访问目标字段：

SELECT
  evaluations.value:channel_meta:agent_first_name[0] AS agent_first_name,
  -- 可选：提取其他关联字段
  evaluations.value:channel_meta:agent_last_name[0] AS agent_last_name,
  evaluations.value:channel_meta:agent_unique_id[0] AS agent_unique_id,
  -- 可选：获取channel_meta下所有字段
  evaluations.value:channel_meta.*
FROM
  test,
  LATERAL FLATTEN(INPUT => PARSE_JSON(src):value:evaluation_forms) AS evaluation_forms,
  LATERAL FLATTEN(INPUT => evaluation_forms.value:evaluations) AS evaluations

语句说明 #

1. 直接在FROM子句中解析src字段为JSON对象，无需额外子查询
2. 第一次FLATTEN展开顶层的evaluation_forms数组
3. 第二次FLATTEN展开每个evaluation_forms元素下的evaluations数组
4. 通过evaluations.value:channel_meta:agent_first_name[0]直接提取数组中的第一个元素（因为agent_first_name是数组类型）

替代解法（兼容原FLATTEN层级） #

如果一定要保留原有的多层FLATTEN，可以通过访问cm.value来获取目标字段，并过滤出channel_meta对应的键值对：

SELECT
  cm.value:agent_first_name[0] AS agent_first_name,
  cm.*
FROM
  (SELECT PARSE_JSON(src) AS src FROM test) t,
  LATERAL FLATTEN(INPUT => SRC:value) v,
  LATERAL FLATTEN(INPUT => v.value) vv,
  LATERAL FLATTEN(INPUT => vv.value) ev,
  LATERAL FLATTEN(INPUT => ev.value) cm
WHERE cm.key = 'channel_meta'

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