如何在Snowflake中无需展开嵌套JSON列表提取指定字段？

现有如下JSON格式的班级数据：

{
  "class": "history",
  "students": [
      {"first_name": "joe", "last_name": "doe", "age": 16},
      {"first_name": "tony", "last_name": "helen", "age": 10},
      {"first_name": "erica", "last_name": "kran", "age": 12}
  ]
}

{
  "class": "math",
  "students": [
      {"first_name": "joe", "last_name": "no", "age": 16},
      {"first_name": "yo", "last_name": "wha", "age": 18},
      {"first_name": "dan", "last_name": "test", "age": 12}
  ]
}

在Elasticsearch中可直接提取students数组内的first_name属性，结果保留在同一行：

class   | first_name
-------------------------------
history | ['joe', 'tony', 'erica']
math    | ['joe', 'yo', 'dan']

在Snowflake中无需通过展开（flatten）再分组的繁琐方式，以下两种方法可实现需求：

1. 生成指定键值的数组 #

使用ARRAY_TRANSFORM函数直接对JSON数组做转换，提取目标键的值，无需拆分再聚合。假设数据存储在表class_data的json_col字段中，查询语句如下：

SELECT
    json_col:class::STRING AS class,
    ARRAY_TRANSFORM(json_col:students, s -> s:first_name::STRING) AS first_name
FROM class_data;

执行后会直接返回每行对应的班级名称，以及由first_name组成的字符串数组，输出效果与Elasticsearch一致。

2. 保留仅含指定键的JSON列表 #

如果需要保留JSON对象结构（仅包含first_name键），可结合OBJECT_CONSTRUCT生成新的JSON数组：

SELECT
    json_col:class::STRING AS class,
    ARRAY_TRANSFORM(json_col:students, s -> OBJECT_CONSTRUCT('first_name', s:first_name)) AS students_first_name
FROM class_data;

输出的students_first_name字段内容为：

[{"first_name":"joe"},{"first_name":"tony"},{"first_name":"erica"}]

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