在Snowflake中如何按空字符串分割解析JSON数组？

给定如下JSON数组，需要按其中的空字符串""作为分隔符，将原数组拆分为多个子数组：

[
  "JOE_SMITH 14:29:02",
  " CHANGEDBY: JANE_DOE -> JOE_SMITH",
  " CHANGEDATE: 2/21/2024 -> 2/24/2024",
  "",
  "JOE_SMITH 14:29:02",
  " OPENORDER: TRUE -> FALSE"
]

期望输出为两个独立的子数组：

[
  "JOE_SMITH 14:29:02",
  " CHANGEDBY: JANE_DOE -> JOE_SMITH",
  " CHANGEDATE: 2/21/2024 -> 2/24/2024"
]
[
  "JOE_SMITH 14:29:02",
  " OPENORDER: TRUE -> FALSE"
]

解决方案 #

通过展开数组+分组聚合的方式实现，核心步骤如下：

1. 用FLATTEN函数将原JSON数组展开为行，保留元素的索引位置
2. 基于空字符串的位置计算分组ID，遇到空字符串时分组ID递增
3. 按分组ID聚合非空元素，重新组成子数组

完整SQL示例 #

-- 测试数据
WITH test_data AS (
  SELECT PARSE_JSON('[
    "JOE_SMITH 14:29:02",
    " CHANGEDBY: JANE_DOE -> JOE_SMITH",
    " CHANGEDATE: 2/21/2024 -> 2/24/2024",
    "",
    "JOE_SMITH 14:29:02",
    " OPENORDER: TRUE -> FALSE"
  ]') AS original_array
),
-- 展开数组并计算分组ID
flattened AS (
  SELECT
    value,
    index,
    -- 累计空字符串的数量作为分组ID，空字符串本身不参与后续聚合
    SUM(CASE WHEN value = '' THEN 1 ELSE 0 END) OVER (ORDER BY index) AS group_id
  FROM test_data,
       LATERAL FLATTEN(input => original_array)
)
-- 按分组ID聚合非空元素，生成子数组
SELECT
  ARRAY_AGG(value) AS split_array
FROM flattened
WHERE value != ''  -- 排除空字符串
GROUP BY group_id
ORDER BY group_id;

说明 #

• FLATTEN函数将数组的每个元素拆分为单独的行，index字段保留元素在原数组中的顺序
• 窗口函数SUM(...) OVER (ORDER BY index)会累计遇到的空字符串数量，以此作为分组依据，确保空字符串后的元素进入新分组
• ARRAY_AGG(value)将同一分组的元素重新聚合为子数组，最后按分组ID排序保证顺序正确

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