Redshift中如何扁平化未知长度JSON列实现订阅级数据拆分

Redshift可以通过JSON_PARSE（处理字符串类型JSON）结合UNNEST的组合，自动展开未知长度的JSON数组，实现将订单级数据转换为订阅级（每个订阅一行）的需求，不需要手动处理单个元素。

假设你的表结构 #

假设你有一张存储订单数据的表order_subscriptions，包含：

• order_id：订单编号（字符串类型）
• subscriptions_json：存储订阅数组的JSON字符串列

处理字符串类型JSON列的SQL示例 #

SELECT
  o.order_id,
  -- 提取并转换customer_id为整数类型
  JSON_EXTRACT_PATH_TEXT(sub.sub_item, 'customer_id')::INT AS customer_id,
  JSON_EXTRACT_PATH_TEXT(sub.sub_item, 'subscription_id') AS subscription_id
FROM
  order_subscriptions o,
  -- 先解析JSON字符串为数组，再展开每个元素为单独行
  UNNEST(JSON_PARSE(o.subscriptions_json)) AS sub(sub_item)

如果使用Redshift SUPER类型存储JSON #

如果你的JSON列是Redshift的SUPER类型（原生支持JSON结构），可以更简洁地处理：

SELECT
  o.order_id,
  sub_item.customer_id,
  sub_item.subscription_id
FROM
  order_subscriptions o,
  -- 直接展开SUPER类型的数组
  UNNEST(o.subscriptions_super) AS sub(sub_item)

关键函数说明 #

• JSON_PARSE：将JSON格式的字符串转换为Redshift可识别的JSON数组对象
• UNNEST：将数组的每个元素拆分为独立的行，这是实现从订单行转订阅行的核心操作
• JSON_EXTRACT_PATH_TEXT：从单个JSON对象中提取指定字段的值，支持类型转换（如::INT）

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