Generate a JSON string containing the differences in two other JSON strings using T-SQL

Question

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,"Activity":"Testing the Update function","Timestamp":"2017-11-25T14:37:30.3700000"}]

Both have the same properties but two of the properties in the second string have different values than the first (UserId and Activity). Is it possible, in Azure SQL Database T-SQL, to generate a third JSON string that contains the values in the second string that are different from the first? In other words, I'd like a string returned that looks like this:

[{"UserId":2,"Activity":"Testing the Update function"}]

Also, the solution should assume that the properties in the JSON strings are not known. I need this to be a generic solution for any two JSON strings.

MarkD · Accepted Answer · 2017-11-26 08:23:36Z

3

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), '}]')

answered Nov 26, 2017 at 8:23

MarkD

5,3361 gold badge16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Randy Minder Over a year ago

Thank you. Very nice piece of work, and this works under Azure. I don't understand the nuances of JSON that well. What do you mean with your comment, "havent checked types other than 1 and 2; think there's a bool type?"

MarkD Over a year ago

@RandyMinder; the types in JSON for numeric or string determine whether or not the value will be offered in quotes or not. I think that JSON, because of its root in javascript, may have a 3rd type: boolean. Just saying that I have not coded for any eventuality other than string and numeric. Hope that clears things up :)

Randy Minder Over a year ago

Got it. I did a bit of research over the weekend, and a bit of tweeking of your code and got it working the way I need to. Thanks for your help.

MarkD Over a year ago

@RandyMinder - good to hear! If there are any improvements that you have made that may benefit others - feel free to share.

Kasunjith Bimal Over a year ago

@MarkD [{"RowId":102787,"UserId":1,"Activity":"This is another test","Timestamp":"2017-11-25T14:37:30.3700000","a":[{"b":"sssss"},{"b":"dfdfdf"}]}] how to compare this string using this SP

Collectives™ on Stack Overflow

Generate a JSON string containing the differences in two other JSON strings using T-SQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related