如何拆分SQL Server表中JSON列至多列？SQL/Python方案咨询

两种方式都能满足需求，选择取决于你的场景：

• 若需实时处理、超大数据量操作，或希望直接在数据库内完成转换，优先用SQL Server原生方案，无需额外环境，性能更优。
• 若需复杂数据清洗、后续分析可视化，或JSON结构频繁变动，Python pandas更灵活，调试修改更便捷。

SQL Server 实现方法 #

你之前的问题是OPENJSON的WITH子句未指定正确的嵌套JSON路径，导致仅读取顶层结构。以下是正确写法：

SELECT
    t._id AS global_id,
    j.[_id.$oid],
    j.created_at,
    j.[from.nome],
    j.[from.id],
    j.nota
FROM myTable as t
CROSS APPLY OPENJSON(t.myColumn)
WITH (
    [_id.$oid] NVARCHAR(50) '$._id."$oid"', -- 带$的键需用双引号包裹
    created_at VARCHAR(50) '$.created_at',
    [from.nome] NVARCHAR(100) '$.from.nome',
    [from.id] NVARCHAR(100) '$.from.id',
    nota NVARCHAR(MAX) '$.nota'
) AS j;

存入新表的写法 #

若要将拆分结果直接存入新表，只需添加INTO子句：

SELECT
    t._id AS global_id,
    j.[_id.$oid],
    j.created_at,
    j.[from.nome],
    j.[from.id],
    j.nota
INTO normalized_json_data -- 新表名
FROM myTable as t
CROSS APPLY OPENJSON(t.myColumn)
WITH (
    [_id.$oid] NVARCHAR(50) '$._id."$oid"',
    created_at VARCHAR(50) '$.created_at',
    [from.nome] NVARCHAR(100) '$.from.nome',
    [from.id] NVARCHAR(100) '$.from.id',
    nota NVARCHAR(MAX) '$.nota'
) AS j;

Python pandas 实现方法 #

利用json_normalize快速展开嵌套JSON，步骤如下：

import pandas as pd
import pyodbc

# 1. 连接SQL Server读取原表数据
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=你的服务器名;DATABASE=你的数据库;UID=账号;PWD=密码')
df = pd.read_sql_query("SELECT _id AS global_id, myColumn FROM myTable", conn)
conn.close()

# 2. 展开JSON数组并标准化嵌套字段
df['myColumn'] = df['myColumn'].apply(pd.read_json)  # 将JSON字符串转为列表
exploded_df = df.explode('myColumn').reset_index(drop=True)  # 展开数组
normalized_df = pd.json_normalize(exploded_df['myColumn'])  # 标准化嵌套结构

# 3. 关联原表global_id并整理列名
result_df = pd.concat([exploded_df[['global_id']], normalized_df], axis=1)
result_df.rename(columns={
    '_id.$oid': '_id.$oid',
    'created_at': 'created_at',
    'from.nome': 'from.nome',
    'from.id': 'from.id',
    'nota': 'nota'
}, inplace=True)

# 可选：将结果写回SQL Server
# from sqlalchemy import create_engine
# engine = create_engine('mssql+pyodbc://账号:密码@服务器/数据库?driver=SQL+Server')
# result_df.to_sql('normalized_json_data', engine, if_exists='replace', index=False)

print(result_df)

内容的提问来源于stack exchange，提问作者Vinícius Sodré Quadros