Snowflake：如何高效用SQL解析JSON数组提取指定键值并聚合

问题说明 #

我的表中有一个名为location的JSON数组列，示例数据如下：

[
  {
    "row": "8",
    "seat": "30",
    "section": "WJ"
  },
  {
    "row": "8",
    "seat": "32",
    "section": "WJ"
  },
  {
    "row": "8",
    "seat": "34",
    "section": "WJ"
  }
]

需要提取section、row字段，并将所有对应seat聚合为逗号分隔的列表，结果格式如下：

+---------+-----+----------+
| section | row | seats    |
|---------+-----+----------+
|   WJ    |  8  | 30,32,34 |

原本通过LATERAL FLATTEN展开数组后用LISTAGG聚合的方式，在百万级数据下效率极低，原代码如下：

select
        flatten_json.value:section::string as listing_section,
        flatten_json.value:row::string as listing_row,
        listagg(distinct flatten_json.value:seat::string, ',') within group (order by try_to_number(flatten_json.value:seat::string)) as listing_seat
from your_table,
lateral flatten(input => parse_json(location), outer => true) as flatten_json
group by
        flatten_json.value:section,
        flatten_json.value:row

高效实现方案 #

方案1：减少不必要的展开，利用数组函数优化 #

假设每个location数组内的所有元素section和row值一致（如示例数据），可以先提取数组统一的section和row，再针对性提取seat，减少数据处理量：

SELECT
    section,
    row,
    ARRAY_TO_STRING(
        ARRAY_AGG(DISTINCT seat ORDER BY TRY_TO_NUMBER(seat)),
        ','
    ) AS seats
FROM (
    SELECT
        -- 取数组第一个元素的section和row（保证数组内值统一）
        PARSE_JSON(location)[0]:section::STRING AS section,
        PARSE_JSON(location)[0]:row::STRING AS row,
        VALUE:seat::STRING AS seat
    FROM your_table,
    LATERAL FLATTEN(INPUT => PARSE_JSON(location))
)
GROUP BY section, row;

方案2：使用JSON_TABLE替代LATERAL FLATTEN #

JSON_TABLE在批量提取JSON字段时，性能有时优于LATERAL FLATTEN，尤其适合结构化的JSON数组：

SELECT
    section,
    row,
    LISTAGG(DISTINCT seat, ',') WITHIN GROUP (ORDER BY TRY_TO_NUMBER(seat)) AS seats
FROM your_table,
JSON_TABLE(
    PARSE_JSON(location),
    '$[*]'
    COLUMNS (
        section STRING PATH '$.section',
        row STRING PATH '$.row',
        seat STRING PATH '$.seat'
    )
) AS jt
GROUP BY section, row;

性能优化关键要点 #

• 移除冗余DISTINCT：如果业务上能保证seat无重复，直接去掉LISTAGG中的DISTINCT，能大幅提升聚合速度
• 预解析JSON：将location列的类型改为VARIANT（建表或ALTER TABLE修改），避免每次查询调用PARSE_JSON
• 简化排序逻辑：如果无需按数字排序，直接用字符串排序去掉TRY_TO_NUMBER，减少计算开销
• 表结构优化：对大表按section、row字段设置聚类（CLUSTER BY）或分区，提升聚合查询的扫描效率

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