Microsoft SQL Server equivalent of Oracle JSON_EQUAL

Question

Oracle有一个JSON_EQUAL函数，在手册中定义为:

JSON_EQUAL条件是在Oracle 18c中引入的，允许对JSON文档进行比较，而不考虑成员顺序或文档格式。

在Microsoft SQL-server中似乎没有相应的。我错了吗?如果没有，有没有人知道(基于CLR的?)替代方案的特别好的实现?

或者我需要使用CLR自己构建一个这样的函数吗?

需要明确的是，这个问题适用于任何SQL版本以上，包括2016年的JSON支持。如果请求的功能只在2019年，这将是一个完全可以接受的答案。

Answer 1

说实话，如果我必须这样做，我可能只使用SQLCLR，它可能更有效，更容易编写。

你至少可以很容易地比较根对象的变量:

SELECT CASE WHEN json.j1 IS NULL AND json.j2 IS NULL
OR  json.j1 IS NOT NULL AND json.j2 IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM OPENJSON(json.j1) j1
FULL JOIN OPENJSON(json.j2) j2 ON j2.[key] = j1.[key]
WHERE j1.[key] IS NULL OR j2.[key] IS NULL
OR j1.value <> j2.value OR j1.type <> j2.type
) THEN 'Same' ELSE 'Different' END,
j1, j2
FROM json;

,db&lt的在fiddle.uk

---

要进行完整的深度比较，您需要使用递归cte，它们很难编码，而且速度很慢。

给你。我已经尝试添加一堆测试用例到小提琴，我喜欢进一步的测试

你不能在rCTE中使用外连接，但你可以使用APPLY，这就是为什么我们需要两个函数

-- This function compares just single values,
-- returns only non-matching scalars, or objects or arrays
CREATE FUNCTION JSON_COMPARE(@j1 nvarchar(max), @j2 nvarchar(max))
RETURNS TABLE AS RETURN
SELECT
ObjectOrArray = CASE WHEN j1.[type] = j2.[type] AND j1.[type] IN (4,5)
THEN 1 ELSE 0 END,
v1 = CASE WHEN j1.[type] = j2.[type] AND j1.[type] IN (4,5) THEN j1.value END,
v2 = CASE WHEN j1.[type] = j2.[type] AND j2.[type] IN (4,5) THEN j2.value END
FROM OPENJSON(@j1) j1
FULL JOIN OPENJSON(@j2) j2 ON j2.[key] = j1.[key]  -- join by key, or array index
WHERE j1.[key] IS NULL  -- will only be if there is a missing key
OR j2.[key] IS NULL  -- as above
OR j1.[type] <> j2.[type]  -- or different types
OR j1.[type] IN (4,5)    -- we also want all objects and arrays
OR j1.value <> j2.value; -- or they're non-matching scalars

-- We use an rCTE, applying JSON_COMPARE to each level of the Json,
-- and look for only non-matches on the outside
CREATE FUNCTION JSON_EQUAL(@j1 nvarchar(max), @j2 nvarchar(max))
RETURNS TABLE AS RETURN
WITH cte AS (
SELECT
j.ObjectOrArray,
j.v1,
j.v2
FROM JSON_COMPARE('[' + @j1 + ']', '[' + @j2 + ']') j
-- necessary to check root objects' types
UNION ALL
SELECT
j.ObjectOrArray,
j.v1,
j.v2
FROM cte
CROSS APPLY JSON_COMPARE(cte.v1, cte.v2) j
WHERE cte.ObjectOrArray = 1  -- we only need to descend for objects or arrays
)
SELECT IsMatch =
CASE WHEN (@j1 IS NULL AND @j2 IS NULL)  -- both roots are null
OR (@j1 IS NOT NULL AND @j2 IS NOT NULL AND  -- both non null and...
NOT EXISTS (SELECT 1
FROM cte
WHERE ObjectOrArray = 0  -- we only want scalars that don't match
)) THEN 1 ELSE 0 END;

SELECT j.IsMatch, j1, j2
FROM json
CROSS APPLY JSON_EQUAL(j1, j2) j;

,db&lt的在小提琴