如何优化Snowflake中使用lateral flatten的JSON解析慢查询

核心问题根源：多个独立的LATERAL FLATTEN语句对同一数组层级操作时，会生成笛卡尔积，行数随flatten次数呈指数级膨胀，最终导致执行时间暴增。

可行优化方案如下：

• 合并多轮flatten为单次rows数组遍历
  仅对rows标签做一次LATERAL FLATTEN，后续所有嵌套字段直接通过JSON路径访问，不需要对每个子数组单独执行flatten操作。如果需要获取子数组的指定位置元素，直接用下标访问即可，例如row.value:sub_array[0]:field::string，避免无意义的数组展开。
• 启用flatten的outer模式
  将默认的inner模式flatten替换为LATERAL FLATTEN(input => json_data:rows, outer => true)，避免因某条row的字段为空导致的行过滤，减少多轮flatten之间的无效匹配计算。
• 提前裁剪数据减少解析开销
  在查询最外层的WHERE条件中提前过滤rows数组为空、或者不需要处理的JSON数据，同时可以先通过子查询仅提取JSON中的rows字段，丢弃其他无用的JSON字段，降低后续解析的计算量。
• 用物化视图预计算解析结果
  如果该解析逻辑为高频使用场景，创建物化视图提前完成flatten和字段提取操作，后续查询直接访问物化视图即可，无需每次执行实时JSON解析。示例代码：

  CREATE MATERIALIZED VIEW parsed_json_mv AS
  SELECT
    base_table.id,
    row.value:row_id::STRING AS row_id,
    row.value:user_info:name::STRING AS user_name,
    row.value:order_list[0]:order_id::STRING AS first_order_id
  FROM base_table,
  LATERAL FLATTEN(input => base_table.json_data:rows, outer => true) row
  

• 临时调整仓库规格适配大计算量
  当单次处理的rows对象量级极高时，可以临时将执行查询的虚拟仓库规格提升1-2档，Snowflake的JSON解析性能随计算资源扩容线性提升，任务完成后再调回原有规格即可控制成本。

如果确实需要展开同一row下的多个子数组，可通过FLATTEN的PATH参数标记数组来源，避免多轮flatten生成交叉连接。

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