无需使用Dataflows，如何在Azure Data Factory中展平嵌套JSON？

方案1：复制活动+Synapse SQL后续解析（推荐，适配大规模数据） #

步骤1：ADF复制原始JSON到Synapse临时表 #

1. 在Synapse中创建临时存储表，适配超长values字段：

CREATE TABLE dbo.StageRawJSON (
    id INT,
    name NVARCHAR(255),
    values_json NVARCHAR(MAX)
)

2. 配置ADF复制活动：
   • 源：API数据集，配置正确的请求参数以获取完整JSON响应。
   • 映射：将JSON中records.id映射到临时表id字段，records.name映射到name字段，records.values映射到values_json字段（选择JSON字符串类型，避免超长内容截断）。
   • 目标：Synapse数据集，指向刚才创建的临时表。

步骤2：Synapse SQL解析嵌套数据到目标表 #

先创建目标表，匹配所需输出结构：

CREATE TABLE dbo.TargetTable (
    id INT,
    name NVARCHAR(255),
    values_id BIGINT,
    values_value NVARCHAR(255),
    values_user NVARCHAR(255)
)

执行INSERT语句，用OPENJSON展开嵌套的values_json：

INSERT INTO dbo.TargetTable (id, name, values_id, values_value, values_user)
SELECT 
    sr.id,
    sr.name,
    j.[id] AS values_id,
    j.[value] AS values_value,
    ISNULL(j.[user], NULL) AS values_user
FROM dbo.StageRawJSON sr
CROSS APPLY OPENJSON(sr.values_json)
WITH (
    id BIGINT '$.id',
    value NVARCHAR(255) '$.value',
    [user] NVARCHAR(255) '$.user'
) j

方案2：ADF控制流逐行处理（适合小批量数据） #

通过Lookup+ForEach+Copy活动实现嵌套数据展开：

1. Lookup活动：读取API返回JSON的records数组，关闭First row only选项，获取所有顶层记录。
2. ForEach活动：遍历Lookup返回的每条记录：
   • 内部添加复制活动：
     • 源：使用内联数据集，通过动态内容@item().values将当前记录的values数组作为输入。
     • 映射：将values.id、values.value、values.user映射到目标表字段，同时用@item().id和@item().name传递顶层的id和name值。
     • 目标：Synapse数据集，指向最终目标表。

方案3：Synapse直接读取存储的JSON文件（若JSON已存于ADLS） #

如果API输出的JSON已保存到ADLS Gen2等存储，可直接在Synapse中解析导入：

INSERT INTO dbo.TargetTable (id, name, values_id, values_value, values_user)
SELECT 
    r.id,
    r.name,
    v.id AS values_id,
    v.value AS values_value,
    ISNULL(v.[user], NULL) AS values_user
FROM OPENROWSET(
    BULK 'https://youradlsaccount.dfs.core.windows.net/container/path/to/json/file.json',
    FORMAT = 'CSV',
    FIELDTERMINATOR = '0x0b',
    FIELDQUOTE = '0x0b'
) WITH (json_content NVARCHAR(MAX)) AS j
CROSS APPLY OPENJSON(j.json_content, '$.records')
WITH (
    id INT '$.id',
    name NVARCHAR(255) '$.name',
    values_json NVARCHAR(MAX) '$.values' AS JSON
) r
CROSS APPLY OPENJSON(r.values_json)
WITH (
    id BIGINT '$.id',
    value NVARCHAR(255) '$.value',
    [user] NVARCHAR(255) '$.user'
) v

注意事项 #

• 确保Synapse表字段类型适配数据：比如values_id用BIGINT容纳超大数值，values_json用NVARCHAR(MAX)避免超长内容截断。
• 若API返回分页数据，需在ADF中配置分页逻辑（如循环请求分页参数），确保获取完整数据集。

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