如何用ADF等工具将SQL表中JSON列拆分为drugs表及独立列？

一、使用Azure Data Factory (ADF) 实现 #

1. 配置数据源连接
   • 连接存储report表的SQL数据库（Azure SQL/SQL Server均可），完成连接字符串和认证方式的配置。
2. 添加数据流活动
   • 在ADF管道中新增数据流活动，进入数据流编辑器后，引入report表作为源数据集，选中包含嵌套JSON的列（假设列名为drug_data）。
3. 解析JSON并展开嵌套结构
   • 添加解析转换：选择drug_data作为待解析列，输出格式设为层次结构，展开其中的drugs对象，此时会生成对应Codeine、Dapsone等药物的子列。
   • 添加取消透视转换：将药物名称（Codeine、Dapsone等）设为键列，对应的属性集合设为值列，把横向的药物条目转为纵向行数据。
4. 提取扁平属性列
   • 再次添加解析转换，选择上一步生成的属性集合列，输出格式设为扁平结构，指定要提取的bin、name等字段，生成对应的独立列。
5. 写入目标表
   • 添加接收器转换，连接到目标SQL数据库，指定drugs表作为接收端，映射解析后的bin、name等列到目标表字段，配置写入行为（追加/覆盖）。
6. 触发管道运行
   • 保存配置后触发管道，完成数据提取与写入。

二、直接使用SQL语句实现 #

适用于SQL Server 2016+、Azure SQL等支持JSON函数的数据库，操作更直接：

1. 创建目标drugs表 #

先匹配数据结构建表：

CREATE TABLE drugs (
    bin VARCHAR(50),
    name VARCHAR(100),
    -- 根据实际JSON结构添加其他属性列
    report_id INT -- 可选，用于关联原report表的主键
);

2. 提取JSON数据插入目标表 #

假设原表report主键为id，JSON列drug_data中的drugs是键值对结构（键为药物名，值为包含bin、name的对象）：

INSERT INTO drugs (report_id, bin, name)
SELECT 
    r.id AS report_id,
    d.bin,
    d.name
FROM report r
CROSS APPLY OPENJSON(r.drug_data, '$.drugs')
WITH (
    bin VARCHAR(50) '$.bin',
    name VARCHAR(100) '$.name'
) d;

如果drugs是数组结构（比如"drugs": [{"name":"Codeine", "bin":"xxx"}, ...]），调整OPENJSON路径即可：

INSERT INTO drugs (report_id, bin, name)
SELECT 
    r.id AS report_id,
    d.bin,
    d.name
FROM report r
CROSS APPLY OPENJSON(r.drug_data, '$.drugs')
WITH (
    bin VARCHAR(50) '$.bin',
    name VARCHAR(100) '$.name'
) d;

3. 定期同步（可选） #

若需要持续同步数据，可创建SQL代理作业或结合ADF管道，定期执行插入/更新语句。

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