Azure数据工厂从嵌套在json中的json字符串中复制数据

Question

我从第三方API获取数据，该API以JSON有效负载进行响应。但是，此JSON包含另一个JSON对象，存储为包含转义符的字符串。示例：

{
"aggregationType": "IDENTITY",
"outputs": [
{
"name": "Sinusoid|Sinusoid"
}
],
"value": "{"dataX":[1,2,3,4],"dataY":[1,4,9,16]}"
}

在文件的第一部分中，我们有一些常规参数，如"aggregationType"one_answers"outputs"，但最后一个参数"value"是我所说的JSON对象。

我想做的是将"dataX"one_answers"dataY"数组一起输入到SQL数据库上的表中。到目前为止，我还没有找到一种简单的方法。

我尝试过的：

• 使用一个简单的复制活动，但我只能访问整个"value"字段，不能从"dataY"中分离出"dataX"，更不用说数组的单个值了
• 使用查找活动将"value"存储在变量中。从这里，我可以在ADF中获得一个可用的JSON对象，但我找到的将数据发送到DB的唯一方法是使用包含复制活动的ForEach活动。由于dataX和dataY实际上要大得多，所以在我调试时，这似乎需要花费很长时间
• 仅将"value"对象复制到blob，并尝试从中检索数据。这并没有起作用，因为对象最终总是以初始"0"来存储；标记和\转义符

有什么办法绕过这个问题吗？

Answer 1

您可以将值存储在SQL中的暂存类型的表中，然后创建一个存储过程，将对象分离为数组

JSON_Value可以帮助您提取值：

SELECT JSON_VALUE('{"dataX": [1,2,3,4]}', '$.dataX') AS 'Output';

在您的存储过程中，您可以尝试使用上面的查询并在SQL表中插入值

Answer 2

为了进行扩展，在@Pratik Somaya的提示下，我编写了一个存储过程，用于将数据插入到持久表中。

我不得不使用WHILE循环，这感觉不太好，所以我仍在寻找更好的解决方案。

CREATE OR ALTER PROCEDURE dataset_outputs_from_adf
@json_output_value NVARCHAR(MAX),
@output_name NVARCHAR(100)
AS
DECLARE @i INT = 0
WHILE JSON_VALUE(@json_output_value,CONCAT('$.dataX[',@i,']')) IS NOT NULL
BEGIN
INSERT INTO my_table
VALUES (
@output_name,
JSON_VALUE(
@json_output_value,
CONCAT('$.dataX[',@i,']')
),
JSON_VALUE(
@json_output_value,
CONCAT('$.dataY[',@i,']')
)
)
SET @i = @i + 1
END
GO

通过使用输出名称参数化数据工厂，我应该能够使整个过程可重复，而不需要重复代码。

Answer 3

据我所知，您可以检索嵌入的"值"；JSON，但它保留了转义字符。您希望将数组[1,2,3,4]和[1,4,9,16]传递给关系数据库(Microsoft SQL Server？(以存储它们。

嵌入的"；值"；可以使用表达式JSON((转换为可引用的JSON。这将处理转义字符。

@json(variables('payload')).dataX

将按预期返回数组[1,2,3,4]。

如何最好地将其导入SQL Server？我们仅限于ADF支持的活动，这实际上归结为存储过程(SP(。使用表值参数是理想的，但在当前的ADF中是不可能的。因此，我建议将它作为字符串传递给SP。

@string(json(variables('payload')).dataX)

这看起来与上面的大致相同，但将是字符串，而不是数组。

在SP中，有几种方法可以解析此字符串。如果您的版本支持它，STRING_SPLIT很方便。请注意，传递的字符串将保留其前导方括号和尾随方括号。这些可以在ADF或SQL中删除，在哪里并不重要。

由于数据是JSON，因此使用OPENJSON可能更有意义。假设我们将dataX的内容传递给SP参数@dataX varchar(4000)。在SP内部，我们编写

create procedure dbo.HandleData
@dataX varchar(4000),
@dataY varchar(4000)
as
insert dbo.SomeTable(ColumnX, ColumnY)
select x.value, y.value
from OPENJSON(@dataX) x
inner join OPENJSON(@dataY) y
on y.[key] = x.[key];

如果数组可能具有不同的长度，或者存在NULL或非整数值等，则可能需要进一步的代码。当然，OPENJSON的结果集可用于SP内的连接或任何其他目的。

如果原始有效负载中的dataX和dataY最终都在同一个DB表中，则可以调用SP两次，每次调用一次。或者，您可以在ADF中联合它们的阵列并调用SP一次。