SQL Merge jsons

Question

我有一个Id为TemplateValue的表Template，它是nvarchar列

Id  TemplateValue
1   {"WorkflowName": "Test","GlobalParams":[{"Name":"abc","Expression":"testexp"}],"Rules": []}

另一个表Rules有RuleJson列，也是nvarchar

Id  RuleJson
1   {"RuleName":"a","expression":"1=1"}
2   {"RuleName":"b","expression":"1=1"}

我想合并这些表中的json，这样Template表中的Rules数组节点就应该用Rulesjson表中不同行中存在的RuleJson更新。

输出应该像：

{
"WorkflowName": "Test",
"GlobalParams": [
{
"Name": "abc",
"Expression": "testexp"
}
],
"Rules": [
{
"RuleName": "a",
"expression": "1=1"
},
{
"RuleName": "b",
"expression": "1=1"
}
]
}

我尝试使用JSON_MODIFY，但无法按预期合并json。

Answer 1

您可以使用以下方法使用JSON_MODIFY来完成此操作：

SELECT  t.Id,
t.TemplateValue,
NewRules = JSON_QUERY(r.Rules),
CombinedJson = JSON_MODIFY(TemplateValue, 
'append $.Rules', 
JSON_QUERY(r.Rules))
FROM    dbo.Template AS t
OUTER APPLY
(   SELECT  CONCAT('[', STRING_AGG(r.RuleJson, ','), ']') 
FROM    dbo.Rules AS r
) AS r (Rules);

数据库示例<gt；小提琴

不幸的是，除了使用STRING_AGG和CONCAT之外，我还没有找到一种更优雅的方法来创建规则数组，但它会产生所需的结果，所以不会完全糟糕。

Answer 2

您可以尝试使用FOR JSON:构建$.RulesJSON数组

SELECT 
t.Id, 
JSON_MODIFY(t.TemplateValue, '$.Rules', JSON_QUERY(a.Rules)) AS TemplateValue
FROM Template t
OUTER APPLY (
SELECT j.RuleName, j.expression
FROM Rules r
CROSS APPLY OPENJSON(r.RuleJson) WITH (
RuleName nvarchar(max),
expression nvarchar(max)
) j
FOR JSON PATH
) a (Rules)