如何在BigQuery中将数据转换为指定嵌套字典JSON格式？

原查询代码 #

WITH
  sequences AS (
    SELECT 0 as primary_key, 0 AS key1, 1 AS key2, 3 AS value1, 4 AS value2 UNION ALL
    SELECT 0, 1, 0, 8, 5 UNION ALL
    SELECT 0, 1, 1, 1, 2 UNION ALL
    SELECT 1, 2, 1, 1, 1 UNION ALL
    SELECT 1, 2, 3, 4, 4
  ),
  agg AS (
  SELECT
    primary_key,
    key1,
    key2,
    ARRAY_AGG((SELECT AS STRUCT f.* EXCEPT (key1, key2))) AS agg
  FROM sequences AS f
  GROUP BY
    primary_key,
    key1,
    key2
)
SELECT
  a.primary_key,
  a.key1,
  a.key2,
  ANY_VALUE(TO_JSON_STRING((agg))) AS result
FROM
  agg AS a
GROUP BY
  a.primary_key,
  a.key1,
  a.key2

目标嵌套字典格式 #

我们需要将结果转换为以primary_key为顶层键，key1为中层键，key2为内层键，对应value1和value2的嵌套字典结构，示例如下：

{
  "0": {
    "0": {
      "1": {"value1": 3, "value2": 4}
    },
    "1": {
      "0": {"value1": 8, "value2": 5},
      "1": {"value1": 1, "value2": 2}
    }
  },
  "1": {
    "2": {
      "1": {"value1": 1, "value2": 1},
      "3": {"value1": 4, "value2": 4}
    }
  }
}

修改后的查询代码 #

利用BigQuery的TO_JSON_OBJECT和STRUCT函数，分层级构建嵌套字典：

WITH sequences AS (
    SELECT 0 as primary_key, 0 AS key1, 1 AS key2, 3 AS value1, 4 AS value2 UNION ALL
    SELECT 0, 1, 0, 8, 5 UNION ALL
    SELECT 0, 1, 1, 1, 2 UNION ALL
    SELECT 1, 2, 1, 1, 1 UNION ALL
    SELECT 1, 2, 3, 4, 4
),
-- 整理每个key2对应的value结构
key2_values AS (
    SELECT
        primary_key,
        key1,
        key2,
        STRUCT(value1, value2) AS value_struct
    FROM sequences
),
-- 按primary_key和key1分组，构建key2层级的字典
key1_level AS (
    SELECT
        primary_key,
        key1,
        TO_JSON_OBJECT(ARRAY_AGG(STRUCT(CAST(key2 AS STRING), value_struct))) AS key2_dict
    FROM key2_values
    GROUP BY primary_key, key1
),
-- 按primary_key分组，构建顶层嵌套字典
primary_key_level AS (
    SELECT
        primary_key,
        TO_JSON_OBJECT(ARRAY_AGG(STRUCT(CAST(key1 AS STRING), PARSE_JSON(key2_dict)))) AS nested_result
    FROM key1_level
    GROUP BY primary_key
)
SELECT * FROM primary_key_level

逻辑说明 #

1. key2_values层：将每行的value1和value2封装为一个STRUCT，作为后续字典的基础值。
2. key1_level层：按primary_key和key1分组，使用TO_JSON_OBJECT将key2（转换为字符串，符合JSON键的要求）与对应的value_struct数组转换为内层字典。
3. primary_key_level层：按primary_key分组，再次使用TO_JSON_OBJECT将key1与对应的内层字典转换为顶层嵌套结构，最终得到便于检索的嵌套字典格式。

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