SQL Merge jsons-CodePudding

I have a table Template with Id , TemplateValue which is nvarchar column

Id  TemplateValue

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

And another table Rules has RuleJson column which is also nvarchar

Id  RuleJson

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

I want to merge json from these tables such a way that Rules array node present in Template table should be updated with RuleJson which is present in Rules json table in different rows.

The output should be like:

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

I tried using JSON_MODIFY, but not able to merge the json as expected.

CodePudding user response：

You can do this with JSON_MODIFY using something like the following:

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);

Example on db<>fiddle

Unfortunately I have not found a more elegant way of creating the rule array other than using STRING_AGG and CONCAT, but it produces the required results so it can't be all bad.

CodePudding user response：

You may try to build the $.Rules JSON array using FOR JSON:

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)