Generate A Json String Containing The Differences In Two Other Json Strings Using T-sql

Say I have two JSON strings as follows: [{'RowId':102787,'UserId':1,'Activity':'This is another test','Timestamp':'2017-11-25T14:37:30.3700000'}] [{'RowId':102787,'UserId':2,'Acti

Solution 1:

Have not tried this on Azure, but it seems to work on SQL Server 2017 There is probably a more elegant way to get to the final JSON string other than through string manipulation, perhaps we can update the answer as better ways are found.

-- Expected : [{"UserId":2,"Activity":"Testing the Update function"}]
DECLARE  @jsonA     NVARCHAR(MAX) = '[{"RowId":102787,"UserId":1,"Activity":"This is another test","Timestamp":"2017-11-25T14:37:30.3700000"}]'
        ,@jsonB     NVARCHAR(MAX) = '[{"RowId":102787,"UserId":2,"Activity":"Testing the Update function","Timestamp":"2017-11-25T14:37:30.3700000"}]'
        ,@result    NVARCHAR(MAX) = ''

SELECT   @jsonA = REPLACE(REPLACE(@jsonA, ']', ''), '[', '')
        ,@jsonB = REPLACE(REPLACE(@jsonB, ']', ''), '[', '')

;WITH DSA AS
(
    SELECT *
    FROM OPENJSON(@jsonA)   
)
,DSB AS
(
    SELECT *
    FROM OPENJSON(@jsonB)   
)
SELECT @result  += CONCAT   (
                                 '"', B.[key], '":'
                                ,IIF(B.[type] = 2, B.[value], CONCAT('"', B.[value], '"'))  -- havent checked types other than 1 and 2; think there's a bool type?
                                ,','
                            )

FROM DSA    A
JOIN DSB    B ON A.[key] = B.[key]
WHERE A.[value] != B.[value]

SELECT CONCAT('[{', LEFT(@result, LEN(@result) - 1), '}]')