Snowflake数据库中如何将JSON解析表的列表字段拆分为多行

前置说明 #

假设你的原始表名为 json_parsed_table，包含非数组的基础字段（比如id、创建时间等）、数组类型字段CITY、数组类型字段ORDERS。如果你的两个数组字段实际是JSON字符串格式而非Snowflake原生ARRAY类型，后面方案中需要先调用PARSE_JSON()做类型转换。

方案1：两个数组元素一一对应拆分（保留位置匹配关系） #

如果业务逻辑中CITY数组的第N个元素和ORDERS数组的第N个元素是对应关系，可通过WITH OFFSET匹配索引实现：

SELECT
  t.* exclude (CITY, ORDERS), -- 保留原始表除两个数组字段外的所有字段
  c.value::STRING AS city,
  o.value::STRING AS orders -- 根据实际字段类型调整强制转换的目标类型
FROM json_parsed_table t,
LATERAL FLATTEN(input => t.CITY) WITH OFFSET AS c_idx,
LATERAL FLATTEN(input => t.ORDERS) WITH OFFSET AS o_idx
WHERE c_idx = o_idx;

如果两个数组长度不一致，可改用FULL OUTER JOIN的方式避免丢失数据：

SELECT
  t.* exclude (CITY, ORDERS),
  c.value::STRING AS city,
  o.value::STRING AS orders
FROM json_parsed_table t
LEFT JOIN LATERAL FLATTEN(input => t.CITY) WITH OFFSET AS c_idx ON TRUE
FULL OUTER JOIN LATERAL FLATTEN(input => t.ORDERS) WITH OFFSET AS o_idx ON c_idx = o_idx;

方案2：两个数组全组合拆分（笛卡尔积） #

如果业务需要所有城市和所有订单的排列组合结果，直接嵌套LATERAL FLATTEN即可：

SELECT
  t.* exclude (CITY, ORDERS),
  c.value::STRING AS city,
  o.value::STRING AS orders
FROM json_parsed_table t,
LATERAL FLATTEN(input => t.CITY) c,
LATERAL FLATTEN(input => t.ORDERS) o;

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