在Azure数据工厂流中为JSON输出模式使用表格输入数据中的JSON路径

Question

我已经看到了如何在ADF数据流中使用聚合转换以及静态在派生列转换中指定的层次结构和用于输出自定义JSON的collect()函数。

我想做的是更动态一点。如果我的表格数据包含具有单个属性值的一列，以及具有表示JSON路径的字符串的另一列，我希望将第一列的属性值输出到JSON模式中，那么如何实现这种转换?

表输入:

CREATE TABLE EAV (EntityID int, AttributePath nvarchar(500), AttributeValue nvarchar(max))
INSERT EAV VALUES
(1, 'user.firstName','John'),
(1, 'user.lastName','Doe'),
(1, 'user.address.city','Pittsburgh'),
(1, 'user.address.state','Pennsylvania'),
(2, 'user.firstName','Jane'),
(2, 'user.lastName','Doe'),
(2, 'user.address.city','Pittsburgh'),
(2, 'user.address.state','Pennsylvania');

JSON输出:

{
"user": {
"id" : 1
"firstName": "John",
"lastName": "Doe",
"address": {
"city": "Pittsburgh",
"state": "Pennsylvania"
}
},
"user": {
"id" : 2
"firstName": "Jane",
"lastName": "Doe",
"address": {
"city": "Pittsburgh",
"state": "Pennsylvania"
}
},
}

Answer 1

SQL Server不能很好地处理动态JSON键。您可以对

使用动态SQL。基本上，我们采用不同的路径表达式，将它们支点出来，并使用FOR JSON PATH

选择它们

DECLARE @cols nvarchar(max) = (
SELECT STRING_AGG(QUOTENAME(AttributePath),',')
FROM (SELECT DISTINCT AttributePath FROM EAV) EAV
);
DECLARE @sql nvarchar(max) = N'
SELECT
id = EntityID,
' + @cols + '
FROM EAV
PIVOT (
MAX(AttributeValue) FOR AttributePath IN (
' + @cols + '
)
) pvt
FOR JSON PATH;
';
EXEC sp_executesql @sql;

结果

[
{
"id":1,
"user":{
"address":{
"city":"Pittsburgh",
"state":"Pennsylvania"
},
"firstName":"John",
"lastName":"Doe"
}
},
{
"id":2,
"user":{
"address":{
"city":"Pittsburgh",
"state":"Pennsylvania"
},
"firstName":"Jane",
"lastName":"Doe"
}
}
]

,db&lt的在小提琴