Microsoft SQL Server equivalent of Oracle JSON

Oracle has a JSON_EQUAL function defined in the manual as:

The JSON_EQUAL condition was introduced in Oracle 18c to allow JSON documents to be compared regardless of member order or document formatting.

There seems to be no equivalent in Microsoft SQL-server. Am I mistaken? If not, does anyone know of a particular good implementation of an (CLR based?) alternative?

Or do I need to build a function like that myself using the CLR?

To be clear, this question pertains to any SQL version above and including 2016 that have JSON support. If the asked for function is only in 2019, that would be an perfectly acceptable answer.

CodePudding user response：

To be honest, if I had to do this, I would probably just use SQLCLR, which is probably more efficient and easier to code.

You can at least compare the root object's variables quite easily:

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<>fiddle.uk

To do a full depth comparison, you would need to use recursive CTEs, which prove difficult to code for, and slow.

But here you go. I've tried to add a bunch of test cases to the fiddle, I'd love for further testing

You can't use outer joins in rCTE's, but you can use APPLY, which is why we need two functions

-- 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<>fiddle