如何在Amazon Redshift中优雅解析可变长度JSON数组

嘿，这个场景太常见了——手动一个个指定数组索引提取元素确实反人类，幸好Redshift有专门处理这种半结构化数据的工具，完全不用写上千行重复代码！

核心思路：用JSON_PARSE+UNNEST展开数组 #

Redshift的**超级类型（Super）**和UNNEST函数组合，可以轻松把JSON数组拆成单行事件，不管数组长度是1还是1000，都能自动适配。

具体SQL示例 #

假设你的表名为event_stream，存储批次事件的字段是event_batch（JSON数组字符串），还有记录分钟时间戳的minute_timestamp字段，基础的展开语句如下：

SELECT
  es.minute_timestamp,
  event_item AS raw_event -- 每个元素是单个事件的JSON对象
FROM
  event_stream es,
  -- 将JSON字符串转成Super数组，再展开成单行
  UNNEST(JSON_PARSE(es.event_batch)) AS t(event_item)

进一步提取事件字段 #

如果需要从单个事件JSON里提取具体字段（比如event_id、user_id），可以用JSON_VALUE（比JSON_EXTRACT_PATH_TEXT更简洁）或者JSON_EXTRACT_PATH_TEXT：

SELECT
  es.minute_timestamp,
  JSON_VALUE(event_item, '$.event_id') AS event_id,
  JSON_VALUE(event_item, '$.event_type') AS event_type,
  JSON_VALUE(event_item, '$.user_id') AS user_id,
  -- 其他需要的字段同理
  event_item AS raw_event -- 保留原始JSON方便排查
FROM
  event_stream es,
  UNNEST(JSON_PARSE(es.event_batch)) AS t(event_item)
WHERE
  -- 过滤空数组的情况（可选）
  JSON_PARSE(es.event_batch) IS NOT NULL

注意事项 #

• 版本兼容性：JSON_PARSE是Redshift 1.0.2175及以上版本支持的函数，如果你的集群版本较老，可以用JSON_ARRAY_ELEMENTS替代（语法略有不同，但核心逻辑一致）。
• 空数组处理：如果原表中存在空的JSON数组（比如[]），UNNEST会返回0行，如果你需要保留原时间戳的行，可以用LEFT JOIN：

  SELECT
    es.minute_timestamp,
    COALESCE(event_item, '{}'::super) AS raw_event
  FROM
    event_stream es
  LEFT JOIN UNNEST(JSON_PARSE(es.event_batch)) AS t(event_item) ON TRUE
  

• 性能优化：如果表数据量很大，建议给event_batch字段加适当的压缩（比如LZO压缩），或者考虑将解析后的事件存入单独的表，避免重复解析JSON。

这样处理后，不管你的批次里有多少个事件，都能一次性展开成单行，后续的分析、统计就和处理普通结构化表一样简单了！

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