What would be the T-SQL equivalent of the Oracle JSON_EQUAL Condition?

• I have a table with a column that contains a JSON string.
• I need to query that table to find rows that match a given input JSON string.
• I would like to ignore order and formatting.
  • ‘{ “foo”: “bar” }’ should equal ‘{“foo”:”bar”}’
  • ‘{ “foo”: “bar”, “a”: “b” }’ should equal ‘{ “a”: “b”, “foo”: “bar” }’
• I want to avoid explicitly testing individual values as the level of nesting or number of properties may change over time.

I’m not familiar with Oracle but I have seen reference to its JSON_EQUAL condition which seems to do exactly what I need. I haven’t been able to find anything similar in T-SQL. I’ve tried using JSON_QUERY to compare the entire objects but it seems to basically just do a string comparison so variations in order and whitespace are treated as differences.

e.g. the sample below will return the [AreEqual] value as 0

DECLARE @SourceJSON NVARCHAR(MAX) = '{ "timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "prop1": 3, "prop2": "string"}}'
DECLARE @TargetJSON NVARCHAR(MAX) = '{"timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "prop1": 3, "prop2": "string"}}'

SELECT
x.[source]
,x.[target]
,CASE WHEN x.[source] = x.[target] THEN 1 ELSE 0 END AS [AreEqual]

FROM(
    SELECT
    JSON_QUERY(@SourceJSON) AS [source]
    ,JSON_QUERY(@TargetJSON) AS [target]
) AS x

Is there a way to achieve this through T-SQL?

Answer

There is no direct equivalent to JSON_EQUAL so I think you will have to roll your own. There is a useful example here and I started to work up an example. OPENJSON allows you to convert JSON to a table format without specifying individual field names.

I would say this is just an example that has been lightly tested on just a couple of samples, more as a proof-of-concept. It is not a production ready piece of code and needs to be thoroughly tested. That said, it might provide you a way of comparing JSON:

-- Create an inline table-valued function that shreds JSON
CREATE OR ALTER FUNCTION dbo.tvf_shredJSON
(
    @inputJSON  NVARCHAR(MAX)
)
RETURNS TABLE AS RETURN
(
    WITH cte AS (
    SELECT
        CAST( 1 AS TINYINT ) xlevel,
        ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rn,
        *,
        CAST( NULL AS INT ) AS parentRowId
    FROM OPENJSON( @inputJSON, '$' )

    UNION ALL

    SELECT
        CAST( xlevel + 1 AS TINYINT ), 
        ROW_NUMBER() OVER( ORDER BY ( SELECT NULL ) ) rn,
        CAST( j.[key] AS NVARCHAR(4000) ), 
        CAST( j.[value] AS NVARCHAR(MAX) ),
        CAST( j.[type] AS TINYINT ),
        CAST( c.rn AS INT ) AS parentRowId
    FROM cte c
        CROSS APPLY OPENJSON ( c.[value] ) j
    WHERE c.[type] In ( 4, 5 )
      AND c.xlevel = xlevel
    )
    SELECT *
    FROM cte
    WHERE [type] Not In ( 4, 5 )  -- array (4) or object (5)
)
GO


-- Create a stored proc to receive the two JSONs and compare them
CREATE OR ALTER PROC dbo.usp_compareJSON

    @sourceJSON NVARCHAR(MAX),
    @targetJSON NVARCHAR(MAX),
    @isEqual    BIT OUTPUT,
    @debug      BIT = 0 
AS
SET NOCOUNT ON

    DECLARE @diffFound1 BIT, @diffFound2 BIT

    -- Shred the two pieces of JSON to temp tables for comparison
    CREATE TABLE #tmp (
        jsonSource  VARCHAR(20) NOT NULL,
        rowId       INT IDENTITY PRIMARY KEY,
        xlevel      TINYINT NOT NULL,
        rn          INT NOT NULL,
        [key]       NVARCHAR(4000) NOT NULL,
        [value]     NVARCHAR(MAX) NOT NULL,
        [type]      INT NOT NULL,
        parentRowId INT NULL
    )


    -- Shred source JSON to table
    INSERT INTO #tmp ( jsonSource, xlevel, rn, [key], [value], [type], parentRowId )
    SELECT 'source', xlevel, rn, [key], [value], [type], parentRowId
    FROM dbo.tvf_shredJSON ( @sourceJSON )


    -- Shred target JSON to table
    INSERT INTO #tmp ( jsonSource, xlevel, rn, [key], [value], [type], parentRowId )
    SELECT 'target', xlevel, rn, [key], [value], [type], parentRowId
    FROM dbo.tvf_shredJSON ( @targetJSON )


    -- Comparison 1: source to target
    IF EXISTS (
        SELECT xlevel, [key], [value], [type]
        FROM #tmp
        WHERE jsonSource = 'source'
        EXCEPT  
        SELECT xlevel, [key], [value], [type]
        FROM #tmp
        WHERE jsonSource = 'target'
    )
    BEGIN
        SET @diffFound1 = 1
    END
    ELSE
        SET @diffFound1 = 0


    -- Comparison 2: target to source 
    IF EXISTS (
        SELECT xlevel, [key], [value], [type]
        FROM #tmp
        WHERE jsonSource = 'target'
        EXCEPT  
        SELECT xlevel, [key], [value], [type]
        FROM #tmp
        WHERE jsonSource = 'source'
    )
    BEGIN
        SET @diffFound2 = 1
    END
    ELSE
        SET @diffFound2 = 0


    -- Return the result
    IF @diffFound1 = 0 AND @diffFound2 = 0
        SET @isEqual = 1
    ELSE
        SET @isEqual = 0
    

    -- Show the json differences if required
    IF @debug = 1
    BEGIN
        SELECT xlevel, [key], [value], [type], parentRowId
        FROM #tmp
        WHERE jsonSource = 'source'
        EXCEPT  
        SELECT xlevel, [key], [value], [type], parentRowId
        FROM #tmp
        WHERE jsonSource = 'target'


        SELECT xlevel, [key], [value], [type], parentRowId
        FROM #tmp
        WHERE jsonSource = 'target'
        EXCEPT  
        SELECT xlevel, [key], [value], [type], parentRowId
        FROM #tmp
        WHERE jsonSource = 'source'
    END

RETURN
GO

-- JSON is the same except for spacing
DECLARE @SourceJSON NVARCHAR(MAX) = '{ "timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "prop1": 3, "prop2": "string"}}'
DECLARE @TargetJSON NVARCHAR(MAX) = '{"timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "prop1": 3, "prop2": "string"}}'
DECLARE @isEqual BIT

EXEC dbo.usp_compareJSON @SourceJSON, @TargetJSON, @isEqual OUTPUT, @debug = 1

SELECT @isEqual isEqual
GO


-- JSON is the same except for ordering
DECLARE @SourceJSON NVARCHAR(MAX)
DECLARE @TargetJSON NVARCHAR(MAX)
DECLARE @isEqual BIT

SET @SourceJSON = '{ "timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "foo": "bar", "a": "b" }}'
SET @TargetJSON = '{"timestamp":"2020-11-13T15:21:03.8407089Z", "name": "foo", "some_object":{ "a": "b", "foo": "bar" }}'

EXEC dbo.usp_compareJSON @SourceJSON, @TargetJSON, @isEqual OUTPUT, @debug = 1

SELECT @isEqual isEqual
GO



DECLARE @inputJSON NVARCHAR(MAX) = 
'{
  "id": "WakefieldFamily",
  "parents": [
      { "familyName": "Wakefield", "givenName": "Robin" },
      { "familyName": "Miller", "givenName": "Ben" }
  ],
  "children": [
      {
        "familyName": "Merriam",
        "givenName": "Jesse",
        "gender": "female",
        "grade": 1,
        "pets": [
            { "givenName": "Goofy" },
            { "givenName": "Shadow" }
        ]
      },
      { 
         "familyName": "Miller",
         "givenName": "Lisa",
         "gender": "female",
         "grade": 8 }
  ],
  "address": { "state": "NY", "county": "Manhattan", "city": "NY" },
  "creationDate": 1431620462,
  "isRegistered": false
}'


DECLARE @SourceJSON NVARCHAR(MAX) = @inputJSON
DECLARE @TargetJSON NVARCHAR(MAX) = @inputJSON
DECLARE @isEqual BIT

EXEC dbo.usp_compareJSON @SourceJSON, @TargetJSON, @isEqual OUTPUT, @debug = 1

SELECT @isEqual isEqual


-- Deliberately alter the JSON
SET @SourceJSON = @inputJSON
SET @TargetJSON = JSON_MODIFY( @SourceJSON, '$.children[0].pets[0].givenName', 'wBob' )

EXEC dbo.usp_compareJSON @SourceJSON, @TargetJSON, @isEqual OUTPUT, @debug = 1

SELECT @isEqual isEqual
GO

I was able to do some further testing based on the original JSON_EQUAL script and got the same results. See this gist for the full script.