如何在Azure Data Factory（ADF）管道中将ADL中的嵌套JSON数据写入关联的SQL Server双表

正好之前做过类似的场景，给你整理下在Azure Data Factory（ADF）里实现这个嵌套JSON到关联SQL表的具体步骤，亲测可行：

核心是分两步走：先确保Stores表的商店记录全部就位（避免重复插入），再将嵌套的商品数据关联上对应商店的Store_ID插入到Items表。

• 先创建ADL Gen2数据集，指向你的嵌套JSON文件，记得在格式设置里把JSON的结构类型设为数组，根节点路径填$.stores，方便后续提取数据。
• 创建SQL Server数据集，指向目标数据库，确保ADF有足够权限访问（比如用SQL认证或者托管身份授权）。

因为Stores表有UK_Stores唯一约束，必须避免重复插入已存在的商店名称，推荐两种实现方式：

方式A：Copy Data活动 + SQL MERGE语句（简单高效） #

1. 新建Copy Data活动，源选择你的ADL JSON数据集，在源查询里写JSON路径提取所有商店名称：

   $..stores[*].name
   

   这会把所有商店名称转换成扁平化的单列数据。
2. 目标选择SQL Server数据集，在复制行为里勾选使用查询批量插入，然后写入MERGE语句：

   MERGE INTO dbo.Stores AS Target
   USING (SELECT ? AS Store_Name) AS Source
   ON Target.Store_Name = Source.Store_Name
   WHEN NOT MATCHED THEN
       INSERT (Store_Name) VALUES (Source.Store_Name);
   

   这里的?对应源数据里的Store_Name字段，MERGE会自动跳过已存在的商店。

方式B：Lookup + ForEach + 存储过程（适合需细粒度控制的场景） #

1. 用Lookup活动读取ADL JSON，查询路径同样用$..stores[*].name，获取所有商店名称的数组。
2. 套一个ForEach活动遍历这个数组，内部调用存储过程执行插入逻辑：
   先在SQL Server里创建存储过程：

   CREATE PROCEDURE dbo.InsertStore
       @StoreName VARCHAR(32)
   AS
   BEGIN
       SET NOCOUNT ON;
       IF NOT EXISTS (SELECT 1 FROM dbo.Stores WHERE Store_Name = @StoreName)
       BEGIN
           INSERT INTO dbo.Stores (Store_Name) VALUES (@StoreName);
       END
   END
   

   然后在ForEach里用Stored Procedure活动，把当前遍历的商店名称作为参数传入。

这一步需要把嵌套的商品数据和商店关联，推荐用ADF**数据流（Data Flow）**来处理，嵌套数据的关联和转换更灵活：

1. 新建数据流，源选择ADL JSON数据集，源设置里的JSON路径填$.stores，读取每个商店的完整对象（包含name和items数组）。
2. 添加**展开（Flatten）**转换：选择items作为要展开的数组列，展开后会把每个商品转换成单独的行，同时保留对应的Store_Name字段。
3. 添加**查找（Lookup）**转换：连接到SQL Server的Stores表，关联条件设为Store_Name == stores.Store_Name，这样就能获取到每个商店对应的Store_ID。
4. 添加**接收器（Sink）**转换：指向SQL Server的Items表，映射字段：
   • Item_Name → JSON里的name
   • Item_Price → JSON里的price
   • Item_Store_ID → Lookup得到的Store_ID
     因为Items表有UK_Items唯一约束，建议在接收器设置里选择更新插入（Upsert），或者在展开后加一个去重转换，避免重复商品名称报错。

把上述步骤串成一个完整管道：

• 第一步：执行Stores表的同步活动（Copy Data或Lookup+ForEach）
• 第二步：执行Items表的数据流活动
• 设置两个活动的依赖为成功时触发，确保商店数据全部插入后再处理商品数据。

• 如果JSON里有重复的商店名称，建议在源查询里先去重，比如在Lookup活动的查询里加distinct逻辑。
• 大数据量场景下，建议开启数据流的并行处理，或者调整Copy Data活动的批量插入大小，提升同步效率。
• 测试阶段先用小样本数据验证，确保Item_Store_ID和Store_Name的关联关系正确。

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