生成含有在使用T-SQL其他两个JSON串

Question

说我有两个JSON字符串如下差异的JSON字符串：

[{"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字符串，该字符串包含第二个字符串中与第一个字符串不同的值？换句话说，我想一个字符串返回，看起来像这样：

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

此外，该解决方案应该假设在JSON字符串的属性是不知道。我需要这是任何两个JSON字符串的通用解决方案。

Answer 1

在Azure上没有尝试过，但它似乎在SQL Server 2017上工作 除了通过字符串操作，可能还有一种更优雅的方式来获得最终的JSON字符串，也许我们可以通过更好的方式更新答案。

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

Answer 2

你可以使用：

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

查询：

WITH cte1 AS (
    SELECT * 
    FROM OPENJSON(@json1) 
    WITH (RowId INT, UserId INT, Activity NVARCHAR(MAX), Timestamp DATETIME2(7))) 
, cte2 AS (
    SELECT * 
    FROM OPENJSON(@json2) 
    WITH (RowId INT, UserId INT, Activity NVARCHAR(MAX), Timestamp DATETIME2(7))) 
SELECT 
    RowId = NULLIF(c2.RowId, c1.RowId) 
    ,UserId = NULLIF(c2.UserId, c1.UserId) 
    ,Activity = NULLIF(c2.Activity, c1.Activity) 
    ,Timestamp = NULLIF(c2.Timestamp, c1.Timestamp) 
FROM cte1 c1 
CROSS JOIN cte2 c2 
FOR JSON AUTO; 

DBFiddle Demo

输出：

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