SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE json(j1 nvarchar(max),j2 nvarchar(max))
INSERT json(j1,j2)
VALUES('
{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}','
{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}'),
('
{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}','
{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": null},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}'),
('{"ID" : null}','{"ID" : "null"}'),
('{"ID" : 1}','{"ID" : 1, "SortAs": "SGML"}'),
('{"ID" : null}','{"ID" : null}'),
('[]','{}'),
('[]','[   ]'),
('[1, 2]','[]'),
(NULL,NULL),
(NULL, '{}'),
('{}', '{        }');
11 rows affected

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


IsMatch
j1
j2


1

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

0

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": null},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}

0
{"ID" : null}
{"ID" : "null"}

0
{"ID" : 1}
{"ID" : 1, "SortAs": "SGML"}

1
{"ID" : null}
{"ID" : null}

0
[]
{}

1
[]
[   ]

0
[1, 2]
[]

1
null
null

0
null
{}

1
{}
{        }


DECLARE @path nvarchar(100)='$.menu';
SELECT 
 JSON_QUERY(j1, @path),JSON_QUERY(j2, @path), j.*
FROM (SELECT TOP 1 * FROM json) json
OUTER APPLY JSON_COMPARE(JSON_QUERY(j1, @path),JSON_QUERY(j2, @path))j


(No column name)
(No column name)
ObjectOrArray
v1
v2


{
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}
{
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}
1
{
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
{
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }

IsMatch	j1	j2
1	{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } }}	{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } }}
0	{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": "CreateNewDoc()"}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } }}	{"menu": { "id": "file", "value": "File", "popup": { "menuitem": [ {"value": "New", "onclick": null}, {"value": "Open", "onclick": "OpenDoc()"}, {"value": "Close", "onclick": "CloseDoc()"} ] } }}
0	{"ID" : null}	{"ID" : "null"}
0	{"ID" : 1}	{"ID" : 1, "SortAs": "SGML"}
1	{"ID" : null}	{"ID" : null}
0	[]	{}
1	[]	[ ]
0	[1, 2]	[]
1	null	null
0	null	{}
1	{}	{ }