SQL Server JSON Diff. Checking for differences between JSON documents.

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.

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.

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

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

The Compare_JsonObject function

Now we ought to introduce the actual routine. It is a table-valued function. If it returns nothing, one or other of the JSON documents is badly formed.

Now, this is all I need but it isn’t the end to it. What if you just want to test part of a JSON document against another? That is a feature that isn’t too complicated to add. What if you didn’t follow the JSON standard that the order of array elements is significant? The way this routine works is to assume that every element of these two json documents are different.

You may want a ‘lax’ mode that matched elements in an array regardless of order. It sounds easy, but the references won’t match then so it would take quite a bit of restructuring then. What if you wanted to judge the similarity of objects based only on the values of certain properties? I’ve taken a literal approach here, and there is plenty of opportunity for making something more complex, based on similar principles. Fortunately, the problem of arrays doesn’t crop up so much in SQL Server, because the implementation of JSON can’t easily produce them, and this function is sufficient for my unit-testing needs.


It is useful to be able to check in SQL Server that a JSON document is what you expect and hope it to be. Since you can represent any SQL result as a JSON document, you have a way, using a JSON diff function like this, a very convenient way of checking the result of any SQL statement that produces a result, whether it is using a procedure, function or batch. I’m beginning to wonder if we have a way of making automated database testing somewhat easier!