Comparing JSON in SQL Server

I posted a couple of weeks ago a link to a toy application that would read a stream of data from Companies House (although it did not really do much more than send to the console, after all I did not want to over share).

To make use of that data something to note is that all the streamed data and some of the bulk extracts are in JSON format. There is a good set of documentation around the JSON formats on their website, although they are clear to describe the functionality as being in beta and to assume the JSON format may change over time.

Having worked a lot with SQL I was keen to understand what functionality was built in for working with this in SQL Server to do both bulk analysis and look at changes over time.

Starting from the documentation around the format and using SQL Server built-in JSON handling I was still left with some odd results (records reported to have changed where no fields had changed for example), and it occurred to me that a general-purpose difference check might be a useful tool to have.Checking to see what existed already I found a helpful blog post here;

But reading the limitation on strict ordering of objects in arrays I was aware that I would get some differences that I would need to ignore from that code so thought it would be a useful exercise to extend “Phil Factor’s” script to handle that. I have put my updated version of “Phil’s” script on GitHub, here;

Running this against the Companies House PSC data showed up a cause of some of my funnies, an undocumented middle_name field, this does still leave a few though, something to look at in the new year!