如何在BigQuery中实现JSON键转列值转行并设last_updated为主键？

原始数据 #

data_dict = {
    "entity_id": "switch.plug",
    "state": "on",
    "attributes": {"friendly_name": "smart plug"},
    "last_changed": "2023-06-09T03:56:25.283681+00:00",
    "last_updated": "2023-06-09T03:56:25.283681+00:00",
    "context": {
        "id": "ZZ01",
        "parent_id": None,
        "user_id": "1as2298",
    },
}

需求说明 #

需要将上述字典的键作为行，对应的值作为单元格内容，同时把last_updated的具体值作为唯一的列名（即主键列），最终效果等价于以下Pandas代码的输出。

Pandas实现示例 #

import pandas as pd

# Convert the dictionary to a DataFrame
df = pd.DataFrame.from_dict(data_dict, orient="index").T

# Set 'last_updated' as the primary key
df.set_index("last_updated", inplace=True)

# Transpose the DataFrame
df = df.transpose()

# Print the transformed table
print(df)

Pandas输出结果 #

last_updated                   2023-06-09T03:56:25.283681+00:00
entity_id                                           switch.plug
state                                                        on
attributes                      {'friendly_name': 'smart plug'}
last_changed                   2023-06-09T03:56:25.283681+00:00
context       {'id': 'ZZ01', 'parent_id': None, 'user_id': '...

BigQuery SQL实现方案 #

方案1：静态列名（已知last_updated值） #

如果last_updated的值固定，可直接硬编码列名：

WITH raw_data AS (
  -- 模拟原始字典数据，替换为你的实际表名或数据源
  SELECT 
    'switch.plug' AS entity_id,
    'on' AS state,
    STRUCT('smart plug' AS friendly_name) AS attributes,
    TIMESTAMP('2023-06-09T03:56:25.283681+00:00') AS last_changed,
    TIMESTAMP('2023-06-09T03:56:25.283681+00:00') AS last_updated,
    STRUCT('ZZ01' AS id, NULL AS parent_id, '1as2298' AS user_id) AS context
),
key_value_pairs AS (
  -- 将每个键值对拆分为单独行
  SELECT 'entity_id' AS key, CAST(entity_id AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'state' AS key, CAST(state AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'attributes' AS key, TO_JSON_STRING(attributes) AS value FROM raw_data
  UNION ALL SELECT 'last_changed' AS key, CAST(last_changed AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'last_updated' AS key, CAST(last_updated AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'context' AS key, TO_JSON_STRING(context) AS value FROM raw_data
)
SELECT 
  key,
  MAX(value) AS `2023-06-09T03:56:25.283681+00:00`
FROM key_value_pairs
GROUP BY key
-- 匹配Pandas输出的排序顺序
ORDER BY 
  CASE key 
    WHEN 'last_updated' THEN 1
    WHEN 'entity_id' THEN 2
    WHEN 'state' THEN 3
    WHEN 'attributes' THEN 4
    WHEN 'last_changed' THEN 5
    WHEN 'context' THEN 6
  END;

方案2：动态列名（自动获取last_updated值） #

如果last_updated的值动态变化，用EXECUTE IMMEDIATE实现动态列名：

DECLARE target_column_name STRING;

WITH raw_data AS (
  SELECT 
    'switch.plug' AS entity_id,
    'on' AS state,
    STRUCT('smart plug' AS friendly_name) AS attributes,
    TIMESTAMP('2023-06-09T03:56:25.283681+00:00') AS last_changed,
    TIMESTAMP('2023-06-09T03:56:25.283681+00:00') AS last_updated,
    STRUCT('ZZ01' AS id, NULL AS parent_id, '1as2298' AS user_id) AS context
)
-- 提取last_updated的值作为列名
SELECT CAST(last_updated AS STRING) INTO target_column_name FROM raw_data;

-- 动态生成并执行SQL
EXECUTE IMMEDIATE FORMAT("""
WITH raw_data AS (
  SELECT 
    'switch.plug' AS entity_id,
    'on' AS state,
    STRUCT('smart plug' AS friendly_name) AS attributes,
    TIMESTAMP('2023-06-09T03:56:25.283681+00:00') AS last_changed,
    TIMESTAMP('%s') AS last_updated,
    STRUCT('ZZ01' AS id, NULL AS parent_id, '1as2298' AS user_id) AS context
),
key_value_pairs AS (
  SELECT 'entity_id' AS key, CAST(entity_id AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'state' AS key, CAST(state AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'attributes' AS key, TO_JSON_STRING(attributes) AS value FROM raw_data
  UNION ALL SELECT 'last_changed' AS key, CAST(last_changed AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'last_updated' AS key, CAST(last_updated AS STRING) AS value FROM raw_data
  UNION ALL SELECT 'context' AS key, TO_JSON_STRING(context) AS value FROM raw_data
)
SELECT 
  key,
  MAX(value) AS `%s`
FROM key_value_pairs
GROUP BY key
ORDER BY 
  CASE key 
    WHEN 'last_updated' THEN 1
    WHEN 'entity_id' THEN 2
    WHEN 'state' THEN 3
    WHEN 'attributes' THEN 4
    WHEN 'last_changed' THEN 5
    WHEN 'context' THEN 6
  END;
""", target_column_name, target_column_name);

注意事项 #

• 若数据已存储在BigQuery表中，直接替换raw_data CTE为你的表名即可，无需重复定义字段。
• 嵌套结构（如attributes、context）通过TO_JSON_STRING转为JSON字符串，与Pandas输出格式保持一致。
• 排序逻辑可根据需求调整，示例中为匹配Pandas输出顺序。

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