I'm a bit late to the party, but I'm running into something similar at the moment. I made a solution based off of this problem that will merge top-level JSON items.
Some examples of what this would do:
{"a":1} + {"B":2} = {"a":1,"B":2}
{"x":true,"y":{"a":"b","c":"d"}} + {"y":{"a":"z"}} = {"x":true,"y":{"a":"z"}}
This version would not drill down to merge sub-items (for example, it would not keep the ["y"]["c"] index in my second example). I'd imagine that it could be enhanced to do so, but this was a quick proof-of-concept version and I don't need to worry about those kind of updates for my purposes.
Content:
--- Merge the top-level items of two JSON object strings into one JSON
--- based off of: https://stackoverflow.com/questions/47489030/generate-a-json-string-containing-the-differences-in-two-other-json-strings-usin
DECLARE @jsonA NVARCHAR(MAX) = '{"CommonValue":"OriginalThing", "OldValue": "A", "ComplexValue": {"InnerValue": "ABC"}}'
,@jsonB NVARCHAR(MAX) = '{"CommonValue":"ChangedThing", "NewValue": "B", "Number": 22}'
,@result NVARCHAR(MAX) = ''
--- Catalog of differences.
DECLARE @JsonDiff TABLE
(
OldKey CHAR(128),
OldValue NVARCHAR(MAX),
OldType CHAR(1),
NewKey CHAR(128),
NewValue NVARCHAR(MAX),
NewType CHAR(1)
)
--- Temporary table for output rows.
--- The table could probably clipped out for production stuff.
--- For proof-of-concept, it's useful for querying results
--- before building the JSON string.
DECLARE @JsonData TABLE
(
NewKey CHAR(128),
NewValue NVARCHAR(MAX),
NewType CHAR(1)
)
;WITH DSA AS
(
SELECT *
FROM OPENJSON(@jsonA)
)
,DSB AS
(
SELECT *
FROM OPENJSON(@jsonB)
)
INSERT INTO @JsonDiff (OldKey, OldValue, OldType, NewKey, NewValue, NewType)
SELECT a.[Key] aKey, a.[Value] aValue, a.[Type] aType, b.[Key] bKey, b.[Value] bValue, b.[Type] bType
FROM DSA A
FULL OUTER JOIN DSB B ON A.[key] = B.[key]
INSERT INTO @JsonData (NewKey, NewValue, NewType)
SELECT OldKey as k, OldValue as v, OldType as t
FROM @JsonDiff
WHERE OldKey IS NOT NULL AND NewKey IS NULL
UNION
SELECT NewKey as k, NewValue as v, NewType as t
FROM @JsonDiff
WHERE NewKey IS NOT NULL
--- a few queries for display purposes
--- select * FROM @JsonDiff
select NewKey, NewValue FROM @JsonData
SELECT @result += CONCAT ( '"', TRIM([NewKey]), '":'
,IIF([NewType] = 1, CONCAT('"', [NewValue], '"'), [NewValue]) -- If the item is a string, then add quotes.
,','
)
FROM @JsonData
--- Print the JSON
SELECT CONCAT('{', LEFT(@result, LEN(@result) - 1), '}')
Edit: Here's a slightly more streamlined version of the last bit that removes the need to have @JsonData:
SELECT @result += CONCAT ( '"', TRIM([k]), '":'
,IIF([t] = 1, CONCAT('"', [v], '"'), [v]) -- If the item is a string, then add quotes.
,','
)
FROM
(
SELECT OldKey as k, OldValue as v, OldType as t
FROM @JsonDiff
WHERE OldKey IS NOT NULL AND NewKey IS NULL
UNION
SELECT NewKey as k, NewValue as v, NewType as t
FROM @JsonDiff
WHERE NewKey IS NOT NULL
) as mid
--- Print the JSON
SELECT CONCAT('{', LEFT(@result, LEN(@result) - 1), '}')
