If you are working on any module or routine such as a procedure, function or batch that produces a result, then you will want to compare it with something that you’ve prepared earlier to make sure that it is the same.

It is often necessary to compare objects, particularly when you are working on a routine that generates an object and you want to check that an improvement you’ve made is actually returned the same result. An XML or JSON document is the nearest we get to an object in SQL Server. This is probably as near as you’d want.

Although this routine is designed to compare JSON documents, and takes them as input, it can be used for comparing any results.

First Example

Imagine, if you can, that you are writing a routine that gives you a list of departments for every group within your AdventureWorks organisation. You want to do it the best way according to the version of SQL Server, but you want the two versions to give the same output. Fortunately, we have our table-valued function that compares any two results converted to JSON. Using this, called dbo.Compare_JsonObject, we can simply do this.

SELECT * FROM dbo.Compare_JsonObject(--compares two JSON Documents
-- here is your non-json query. This is just an example using 'string_agg'.
(SELECT GroupName, String_Agg(Name, ', ') WITHIN GROUP ( ORDER BY Name)  AS departments
  FROM AdventureWorks2016.HumanResources.Department
  GROUP BY GroupName
--and we then convert it to JSON
  FOR JSON AUTO),
-- here is your non-json query. This is an 'XML-trick' version to produce the list
(SELECT GroupName,
  (
  SELECT
    Stuff((
    SELECT ', ' + Name
      FROM AdventureWorks2016.HumanResources.Department AS dep
      WHERE dep.GroupName = TheGroup.GroupName ORDER BY dep.name
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'),
  1,2,'')) AS departments
  FROM AdventureWorks2016.HumanResources.Department AS thegroup
  GROUP BY GroupName
--and we then convert it to JSON
  FOR JSON AUTO
  )
)
WHERE SideIndicator <> '==' --meaning ALL the items that don't match
--hopefully, nothing will get returned

Fortunately, we got this right and can slot the new version in place of the old one when we get that long-awaited upgrade to SQL Server. However, we can wind back in time to see the results before we got the list order correct.

SELECT * FROM dbo.Compare_JsonObject(--compares two JSON Documents
-- here is your non-json query. This is just an example using 'string_agg'.
(SELECT GroupName, String_Agg(Name, ', ') AS departments
  FROM AdventureWorks2016.HumanResources.Department
  GROUP BY GroupName
--and we then convert it to JSON
  FOR JSON AUTO),
-- here is your non-json query. This is an 'XML-trick' version to produce the list
(SELECT GroupName,
  (
  SELECT
    Stuff((
    SELECT ', ' + Name
      FROM AdventureWorks2016.HumanResources.Department AS dep
      WHERE dep.GroupName = TheGroup.GroupName
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'),
  1,2,'')) AS departments
  FROM AdventureWorks2016.HumanResources.Department AS thegroup
  GROUP BY GroupName
--and we then convert it to JSON
  FOR JSON AUTO
  )
)
WHERE SideIndicator <> '==' --meaning ALL the items that don't match

which gives two mismatches, produced by different orders of the list items

We can see what doesn’t match, and we can see why: the ordering for those two rows is wrong.

Second Example

We could, of course, use variables to store our JSON

DECLARE @SourceJSON NVARCHAR(MAX) = '{

  "question": "What is a clustered index?",
  "options": [
    "A bridal cup used in marriage ceremonies by the Navajo indians",
    "a bearing in a gearbox used to facilitate double-declutching",
    "An index that sorts and store the data rows in the table or view based on the key values"
  ],
  "answer": 3
}',
@TargetJSON NVARCHAR(MAX) = '{

  "question": "What is a clustered index?",
  "options": [
	"a form of mortal combat referred to as ''the noble art of defense''",
    "a bearing in a gearbox used to facilitate double-declutching",
	"A bridal cup used in marriage ceremonies by the Navajo indians",
    "An index that sorts and store the data rows in the table or view based on the key values"

  ],
  "answer": 4
}'
SELECT SideIndicator, ThePath, TheKey, TheSourceValue, TheTargetValue
  FROM dbo.Compare_JsonObject(@SourceJSON, @TargetJSON) AS Diff;

which gies us all the differences

That side-indicator column is telling us things. A match is ‘==’, a difference is ‘<>’, a missing property in the source is ‘->’ and a missing property in the target is ‘<-‘

The ‘path’ column can be used to get the value of a property or to modify it. It uses the same convention as the SQL Server JSON functions.

There is also a parent column that is useful for doing more detailed comparison, or doing it at a particular level. Here, you can eliminate all values that are the same and still see what question had a different answer to it

SELECT ThePath, TheKey, TheSourceValue, TheTargetValue,
  Json_Value(@SourceJSON, TheParent + '.question') AS TheQuestion
  FROM dbo.Compare_JsonObject(@SourceJSON, @TargetJSON)
  WHERE SideIndicator = '<>';

#blogs #json #sql #sql-server #json-diff