Snowflake中如何按KEY列聚合构造指定嵌套OBJECT对象？

问题背景 #

现有表A包含Priority_A、Priority_B、SOURCE、KEY列，数据如下：

Priority_A    Priority_B      SOURCE      KEY
    1            1              A         Z
   NULL          NULL          NULL       Z
   NULL          2              B         Z
   NULL          NULL          NULL       Y
   NULL          NULL          NULL       Y
   NULL          3              C         Y
   NULL          3              C         Z

期望基于KEY列构造单个嵌套OBJECT对象，结构如下：

{
  "Z": {
    "PRIORITY_B": {
      "1": "A",
      "2": "B",
      "3": "C"
    },
    "PRIORITY_A": {
      "1": "A"
    }
  },
  "Y": {
    "PRIORITY_B": {
      "3": "C"
    }
  }
}

尝试执行以下语句后得到7行独立OBJECT，未达到目标结构：

SELECT OBJECT_CONSTRUCT(KEY, OBJECT_CONSTRUCT(PRIORITY_A, SOURCE, PRIORITY_B, SOURCE)) AS Prio FROM A

若无法实现单个对象，接受按KEY分为两行的OBJECT结构，询问Snowflake中是否可行。

实现方案 #

完全可行，核心思路是先过滤无效数据，再按KEY分组聚合键值对，最后嵌套构造OBJECT，以下是具体实现：

1. 按KEY分两行的OBJECT结构（基础版） #

该方案先过滤掉SOURCE为NULL的无效行，再按KEY分组聚合Priority_A和Priority_B的键值对：

SELECT 
  KEY,
  OBJECT_CONSTRUCT(
    'PRIORITY_A', OBJECT_AGG(KEY_PA, VAL_PA) IGNORE NULLS,
    'PRIORITY_B', OBJECT_AGG(KEY_PB, VAL_PB) IGNORE NULLS
  ) AS key_obj
FROM (
  SELECT 
    KEY,
    -- 提取PRIORITY_A的键和值
    OBJECT_KEYS(pa_obj)[0] AS KEY_PA,
    OBJECT_VALUES(pa_obj)[0] AS VAL_PA,
    -- 提取PRIORITY_B的键和值
    OBJECT_KEYS(pb_obj)[0] AS KEY_PB,
    OBJECT_VALUES(pb_obj)[0] AS VAL_PB
  FROM (
    SELECT 
      KEY,
      IFF(Priority_A IS NOT NULL, OBJECT_CONSTRUCT(Priority_A::STRING, SOURCE), NULL) AS pa_obj,
      IFF(Priority_B IS NOT NULL, OBJECT_CONSTRUCT(Priority_B::STRING, SOURCE), NULL) AS pb_obj
    FROM A
    WHERE SOURCE IS NOT NULL
  )
)
GROUP BY KEY;

执行结果示例：

2. 单个嵌套OBJECT结构（进阶版） #

在基础版外层再套一层OBJECT_AGG，即可将所有KEY合并为一个顶级OBJECT：

SELECT OBJECT_AGG(KEY, key_obj) AS final_obj
FROM (
  SELECT 
    KEY,
    OBJECT_CONSTRUCT(
      'PRIORITY_A', OBJECT_AGG(KEY_PA, VAL_PA) IGNORE NULLS,
      'PRIORITY_B', OBJECT_AGG(KEY_PB, VAL_PB) IGNORE NULLS
    ) AS key_obj
  FROM (
    SELECT 
      KEY,
      OBJECT_KEYS(pa_obj)[0] AS KEY_PA,
      OBJECT_VALUES(pa_obj)[0] AS VAL_PA,
      OBJECT_KEYS(pb_obj)[0] AS KEY_PB,
      OBJECT_VALUES(pb_obj)[0] AS VAL_PB
    FROM (
      SELECT 
        KEY,
        IFF(Priority_A IS NOT NULL, OBJECT_CONSTRUCT(Priority_A::STRING, SOURCE), NULL) AS pa_obj,
        IFF(Priority_B IS NOT NULL, OBJECT_CONSTRUCT(Priority_B::STRING, SOURCE), NULL) AS pb_obj
      FROM A
      WHERE SOURCE IS NOT NULL
    )
  )
  GROUP BY KEY
);

执行结果与期望结构完全一致：

{
  "Z": {
    "PRIORITY_A": {"1": "A"},
    "PRIORITY_B": {"1": "A", "2": "B", "3": "C"}
  },
  "Y": {
    "PRIORITY_B": {"3": "C"}
  }
}

关键说明 #

• 使用IGNORE NULLS参数可自动过滤无数据的节点（如Y的PRIORITY_A会被忽略）
• 将Priority_A/Priority_B转为STRING类型，避免数字作为键时出现类型兼容问题

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