如何在Snowflake中将多列同键Object解析为行格式

一、转换为长格式（行转列） #

针对需求，只需FLATTEN其中一个Object列获取时间周期的KEY，再通过KEY直接提取其他Object列对应的值即可，这种方式无需多次JOIN，性能更优且支持扩展任意数量的Object列。

示例代码 #

WITH smpl AS (
SELECT
  '12a' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 26,
    'd1912', 6,
    'd2001', 73) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 260.1,
    'd1912', 30,
    'd2001', 712.3) AS activity_duration
UNION ALL
SELECT
  '13b' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2,
    'd1912', 3,
    'd2001', 4) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2.2,
    'd1912', 3.3,
    'd2001', 4.3) AS activity_duration
)
SELECT
  s.customer_id,
  f.key AS time_period,
  -- 通过KEY提取对应Object列的值
  s.activity_count[f.key]::INT AS activity_count,
  s.activity_duration[f.key]::NUMERIC AS activity_duration
FROM smpl s
-- 仅FLATTEN一个Object列获取所有时间周期
LATERAL FLATTEN(input => s.activity_count) f;

说明 #

• 只需FLATTEN任意一个包含完整时间周期的Object列（比如activity_count），就能得到所有time_period值
• 其他Object列通过列名[KEY]的方式直接取值，新增列时只需复制这一行格式修改列名即可，完美支持50+列扩展

二、转换为宽格式（列转行，类似BigQuery的.*展开） #

Snowflake中可以通过FLATTEN + PIVOT实现动态将Object的KEY转为列名，也可以直接用列名:KEY的方式手动指定列（适合已知KEY的场景）。

方式1：动态PIVOT（自动识别所有KEY） #

WITH smpl AS (
SELECT
  '12a' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 26,
    'd1912', 6,
    'd2001', 73) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 260.1,
    'd1912', 30,
    'd2001', 712.3) AS activity_duration
UNION ALL
SELECT
  '13b' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2,
    'd1912', 3,
    'd2001', 4) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2.2,
    'd1912', 3.3,
    'd2001', 4.3) AS activity_duration
),
-- 先将activity_count展开为长格式
count_flat AS (
  SELECT customer_id, key, value::INT AS count_val
  FROM smpl, LATERAL FLATTEN(input => activity_count)
),
-- 再将activity_duration展开为长格式
duration_flat AS (
  SELECT customer_id, key, value::NUMERIC AS duration_val
  FROM smpl, LATERAL FLATTEN(input => activity_duration)
)
-- 分别PIVOT两个指标
SELECT
  p1.customer_id,
  -- 重命名列，加上指标前缀
  p1.d1910 AS activity_count_d1910,
  p1.d1911 AS activity_count_d1911,
  p1.d1912 AS activity_count_d1912,
  p1.d2001 AS activity_count_d2001,
  p2.d1910 AS activity_duration_d1910,
  p2.d1911 AS activity_duration_d1911,
  p2.d1912 AS activity_duration_d1912,
  p2.d2001 AS activity_duration_d2001
FROM (
  SELECT * FROM count_flat PIVOT(MAX(count_val) FOR key IN ('d1910','d1911','d1912','d2001'))
) p1
JOIN (
  SELECT * FROM duration_flat PIVOT(MAX(duration_val) FOR key IN ('d1910','d1911','d1912','d2001'))
) p2 ON p1.customer_id = p2.customer_id;

方式2：手动指定列（适合已知KEY的场景） #

如果已经明确Object中的KEY，可以直接用列名:KEY提取，写法更简洁：

WITH smpl AS (
SELECT
  '12a' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 26,
    'd1912', 6,
    'd2001', 73) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 0,
    'd1911', 260.1,
    'd1912', 30,
    'd2001', 712.3) AS activity_duration
UNION ALL
SELECT
  '13b' AS customer_id,
  OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2,
    'd1912', 3,
    'd2001', 4) as activity_count,
   OBJECT_CONSTRUCT(
    'd1910', 1,
    'd1911', 2.2,
    'd1912', 3.3,
    'd2001', 4.3) AS activity_duration
)
SELECT
  customer_id,
  activity_count:d1910::INT AS activity_count_d1910,
  activity_count:d1911::INT AS activity_count_d1911,
  activity_count:d1912::INT AS activity_count_d1912,
  activity_count:d2001::INT AS activity_count_d2001,
  activity_duration:d1910::NUMERIC AS activity_duration_d1910,
  activity_duration:d1911::NUMERIC AS activity_duration_d1911,
  activity_duration:d1912::NUMERIC AS activity_duration_d1912,
  activity_duration:d2001::NUMERIC AS activity_duration_d2001
FROM smpl;

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