Azure Data Factory如何映射SQL查询结果构造JSON传入存储过程

根据查询结果的数据量选对应方案即可，不需要额外写自定义代码，全用ADF原生活动就能实现。 #

方案1：轻量活动组合方案（适配查询结果≤10万行场景，配置最快） #

• 第一步：添加查找活动
  • 源数据集绑定存储FileGroups表的SQL数据库连接
  • 查询模式选「自定义查询」，执行语句拉取指定列：

    SELECT Id, Name, Version FROM FileGroups
    

  • 取消勾选「仅第一行」选项，若结果超过5000行，打开设置里的「分页」开关，单查找活动最多支持拉取10万行结果。
• 第二步：配置JSON结构拼接逻辑
  • 先在管道变量面板新建一个数组类型变量，命名为finalJsonCollection
  • 添加ForEach活动，遍历项配置为查找活动的输出集合：@activity('查找FileGroups数据').output.value
  • 在ForEach活动内部添加追加变量活动，给数组追加单条结构化JSON对象，值用以下表达式生成：

    @json(concat(
        '{"id":"', replace(toString(item().Id), '"', '\\"'),
        '","name":"', replace(toString(item().Name), '"', '\\"'),
        '","version":', item().Version,
        ',"pipeline_param_1":"', replace(pipeline().parameters.pipeline_param_1, '"', '\\"'),
        ',"pipeline_param_2":"', replace(pipeline().parameters.pipeline_param_2, '"', '\\"'),'}'
    ))
    

  表达式里提前对字符串字段做了双引号转义，避免字段值含特殊字符导致JSON格式失效。
• 第三步：对接存储过程活动
  • 在ForEach活动后添加存储过程活动，绑定目标数据库的对应存储过程
  • 如果你需要的入参是示例里逗号分隔的JSON对象串（无外层数组中括号），入参值填以下表达式即可：

    @substring(string(variables('finalJsonCollection')), 1, sub(length(string(variables('finalJsonCollection'))), 2))
    

  • 如果存储过程直接支持JSON数组入参，直接传@string(variables('finalJsonCollection'))就行。

踩坑提示：存储过程接收JSON的入参类型请设为NVARCHAR(MAX)或Azure SQL原生JSON类型，避免长字符串被截断。

方案2：映射数据流方案（适配10万行以上大数据量场景） #

如果查询结果超过10万行，用数据流做转换性能更稳定：

• 第一步：添加数据流活动，提前在数据流参数面板新增两个和管道参数同名的字符串类型参数，在管道侧把全局管道参数的值传入数据流参数。
• 第二步：添加源转换，数据集绑定FileGroups所在的SQL库，源查询模式选「SQL查询」，执行SELECT Id, Name, Version FROM FileGroups拉取源数据。
• 第三步：添加选择转换，把原有列重命名为JSON需要的键名：Id改id、Name改name、Version改version，同时过滤掉不需要的多余列。
• 第四步：添加派生列转换，新增两个固定列：
  • 列名pipeline_param_1，值填数据流表达式$pipeline_param_1
  • 列名pipeline_param_2，值填数据流表达式$pipeline_param_2
  • 所有字符串类型列统一用escapeJson()函数做特殊字符转义，避免JSON格式错误。
• 第五步：添加缓存接收器，把转换完成的结果集缓存回管道；或者直接配置接收器为目标SQL库的存储过程，把转换后的JSON结构直接映射到存储过程入参即可。

格式校验提示 #

拼接完成后可以在管道调试模式下把构造的JSON字符串输出到输出面板，和你预期的结构做比对，重点检查数值类型的Version字段有没有被误加引号变成字符串、特殊字符有没有正常转义即可。

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