Snowflake中嵌套array_agg实现JSON转换报错求助

需要对嵌套对象数组的JSON做结构转换：将每个对象中b数组内的c值提取出来，组成新的d数组。尝试用嵌套array_agg操作时，始终触发报错：SQL compilation error: Unsupported subquery type cannot be evaluated。目标是生成包含转换后JSON数组的表列，相关输入输出示例如下：

输入示例SQL #

with table_1 as (
    select parse_json(
      '[
          {
              "a": "a_value_1",
              "b": [
                  {
                      "c": "c_value_1"
                  },
                  {
                      "c": "c_value_2"
                  }
              ]
          },
          {
              "a": "a_value_2",
              "b": [
                  {
                      "c": "c_value_2"
                  },
                  {
                      "c": "c_value_3"
                  }
              ]
          }
      ]'
    ) as json_object
)
select json_object from table_1;

期望输出示例 #

[
  {
      "a": "a_value_1",
      "d": [ "c_value_1", "c_value_2"]
  },
  {
      "a": "a_value_2",
      "d": [ "c_value_2", "c_value_3"]
  }
]

在Snowflake中，不要使用嵌套子查询的array_agg，而是通过两层FLATTEN展开嵌套数组，再逐步聚合构造目标JSON：

with table_1 as (
    select parse_json(
      '[
          {
              "a": "a_value_1",
              "b": [
                  {
                      "c": "c_value_1"
                  },
                  {
                      "c": "c_value_2"
                  }
              ]
          },
          {
              "a": "a_value_2",
              "b": [
                  {
                      "c": "c_value_2"
                  },
                  {
                      "c": "c_value_3"
                  }
              ]
          }
      ]'
    ) as json_object
),
-- 展开顶层JSON数组，拆分出每个独立对象
flatten_top as (
    select value as item
    from table_1,
         lateral flatten(input => json_object)
),
-- 展开每个对象的b数组，提取c值
flatten_b as (
    select 
        item:a::string as a_val,
        b_item:c::string as c_val
    from flatten_top,
         lateral flatten(input => item:b) as b_item
),
-- 按a值聚合c值，生成d数组
aggregated as (
    select 
        a_val,
        array_agg(c_val) as d_array
    from flatten_b
    group by a_val
)
-- 构造最终的JSON数组
select array_agg(object_construct('a', a_val, 'd', d_array)) as transformed_json
from aggregated;

步骤说明 #

1. 第一层FLATTEN：展开顶层JSON数组，将每个对象拆分为单独行；
2. 第二层FLATTEN：展开每个对象内的b数组，提取所有c字段值；
3. 聚合阶段：按a字段分组，用array_agg将同组的c值合并为d数组；
4. 最终构造：用object_construct生成每个目标结构对象，再通过array_agg组合成完整的JSON数组。

这种方式能规避嵌套子查询的限制，正确生成目标JSON结构。

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