如何在SQL或Python中展开SQL表列中的嵌套JSON数据

嘿，我太懂你这种需求了——手里的SQL表混着普通字段和嵌套JSON列，想把那些藏在JSON里的key都拆成单独的列，还得保留原来的id、event_name这些核心数据对吧？我给你分SQL和Python两种常用场景，唠唠实际工作里怎么操作，都是亲测好用的方法~

一、用SQL处理（以BigQuery为例，其他支持JSON的SQL引擎逻辑类似） #

首先得把嵌套的JSON数组先“拆平”，再把不同的key转成列。如果你的event_params是字符串类型，得先转成JSON数组；如果已经是数组类型，直接拆就行。

1. 手动指定要展开的key（适合key数量少的情况） #

WITH unnested_data AS (
  SELECT
    id,
    event_name,
    param.key AS param_key,
    -- 提取value里的非空值，因为每个value里只有一个字段有数据
    COALESCE(
      param.value.string_value,
      CAST(param.value.int_value AS STRING),
      CAST(param.value.float_value AS STRING),
      CAST(param.value.double_value AS STRING)
    ) AS param_value
  FROM
    your_table,
    -- 如果event_params是字符串，用JSON_EXTRACT_ARRAY转成数组；如果本身是数组，直接写UNNEST(event_params)
    UNNEST(JSON_EXTRACT_ARRAY(event_params)) AS param
)
SELECT
  id,
  event_name,
  -- 把每个key转成对应的列
  MAX(IF(param_key = 'name_attribute1', param_value, NULL)) AS name_attribute1,
  MAX(IF(param_key = 'name_attribute2', param_value, NULL)) AS name_attribute2,
  -- 有其他key就继续加这行格式的语句就行
FROM unnested_data
GROUP BY id, event_name;

2. 动态展开所有key（适合key数量多、不确定的情况） #

如果key太多不想手动写，BigQuery可以用动态SQL自动生成透视语句：

DECLARE param_keys ARRAY<STRING>;

-- 先获取所有不同的param_key
SET param_keys = ARRAY(
  SELECT DISTINCT param.key
  FROM your_table,
  UNNEST(JSON_EXTRACT_ARRAY(event_params)) AS param
);

-- 拼接动态透视的SQL语句
EXECUTE IMMEDIATE '''
WITH unnested_data AS (
  SELECT
    id,
    event_name,
    param.key AS param_key,
    COALESCE(
      param.value.string_value,
      CAST(param.value.int_value AS STRING),
      CAST(param.value.float_value AS STRING),
      CAST(param.value.double_value AS STRING)
    ) AS param_value
  FROM your_table,
  UNNEST(JSON_EXTRACT_ARRAY(event_params)) AS param
)
SELECT
  id,
  event_name,
  ''' || ARRAY_TO_STRING(ARRAY(SELECT 'MAX(IF(param_key = "' || key || '", param_value, NULL)) AS `' || key || '`' FROM UNNEST(param_keys) AS key), ', ') || '''
FROM unnested_data
GROUP BY id, event_name;
''';

二、用Python（Pandas）处理 #

如果更习惯用Python做数据处理，用Pandas就能轻松搞定，步骤大概是“读数据→转JSON→拆数组→透视转列”：

import pandas as pd
import json

# 第一步：从SQL数据库读取数据，这里假设你已经有数据库连接了
df = pd.read_sql("SELECT * FROM your_table", your_database_connection)

# 注意：你的event_params格式不是标准JSON（是key=xxx而不是"key":"xxx"），得先转成标准格式
def fix_non_standard_json(json_str):
    # 把key=xxx替换成标准JSON的键值对格式
    fixed_str = json_str.replace('key=', '"key":').replace('value={', '"value":{')
    fixed_str = fixed_str.replace('string_value=', '"string_value":').replace('int_value=', '"int_value":')
    fixed_str = fixed_str.replace('float_value=', '"float_value":').replace('double_value=', '"double_value":')
    # 处理null和括号，确保能被json.loads解析
    fixed_str = fixed_str.replace('null', 'null').replace('}', '}').replace('{', '{')
    return fixed_str

# 把event_params列转成Python的列表字典
df['event_params'] = df['event_params'].apply(fix_non_standard_json).apply(json.loads)

# 第二步：把嵌套的数组拆成单独的行
df_exploded = df.explode('event_params').reset_index(drop=True)

# 第三步：提取每个param的key和对应的有效value
df_exploded['param_key'] = df_exploded['event_params'].apply(lambda x: x['key'])
df_exploded['param_value'] = df_exploded['event_params'].apply(lambda x: 
    # 取第一个非空的value值
    x['value']['string_value'] or x['value']['int_value'] or x['value']['float_value'] or x['value']['double_value']
)

# 第四步：透视，把key转成列，合并回原有的id和event_name
df_pivoted = df_exploded.pivot(index=['id', 'event_name'], columns='param_key', values='param_value').reset_index()

# 现在df_pivoted就是你要的结构啦，每个key对应一列
print(df_pivoted.head())

如果你的event_params已经是标准JSON格式，那可以跳过fix_non_standard_json这个函数，直接用json.loads解析就行。

备注：内容来源于stack exchange，提问作者João Bugelli