使用T-SQL生成包含其他两个JSON字符串中的差异的JSON字符串 [英] Generate a JSON string containing the differences in two other JSON strings using T-SQL

问题描述

说我有两个JSON字符串，如下所示:

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"}]

两者都具有相同的属性，但是第二个字符串中的两个属性具有与第一个不同的值(UserId和Activity).在Azure SQL数据库T-SQL中是否可以生成第三个JSON字符串，该字符串包含第二个字符串中与第一个不同的值?换句话说，我想要一个返回的字符串，如下所示:

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"}]

此外，该解决方案还应假定JSON字符串中的属性未知.我需要将其作为任何两个JSON字符串的通用解决方案.

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.

推荐答案

尚未在Azure上尝试过此方法，但它似乎在SQL Server 2017上有效 除了通过字符串操作，可能还有一种更优雅的方法来获取最终的JSON字符串，也许我们可以在找到更好的方法时更新答案.

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

这篇关于使用T-SQL生成包含其他两个JSON字符串中的差异的JSON字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！