{"id":87545,"date":"2020-07-06T17:59:53","date_gmt":"2020-07-06T17:59:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=87545"},"modified":"2026-04-17T15:48:59","modified_gmt":"2026-04-17T15:48:59","slug":"sql-server-json-diff-checking-for-differences-between-json-documents","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-server-json-diff-checking-for-differences-between-json-documents\/","title":{"rendered":"SQL Server JSON Diff: Compare JSON Documents in T-SQL"},"content":{"rendered":"\n

Comparing two JSON documents in SQL Server – checking whether a stored procedure or query produces the JSON output you expect – can be done entirely in T-SQL using a table-valued function that walks the JSON hierarchy and returns a structured difference report. Each row in the result uses an indicator column: == for exact matches, <> for value differences, -> for properties present in the target but missing from the source, and <- for properties present in the source but missing from the target. This article presents the Compare_JsonObject function and two worked examples: comparing department query output across SQL Server versions, and diffing stored JSON variables.<\/strong><\/p>\n\n\n\n

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\u2019ve prepared earlier to make sure that it is the same.<\/p>\n\n\n\n

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\u2019ve 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\u2019d want.<\/p>\n\n\n\n

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

First Example<\/h1>\n\n\n\n

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.<\/p>\n\n\n\n

SELECT * FROM dbo.Compare_JsonObject(--compares two JSON Documents\n-- here is your non-json query. This is just an example using 'string_agg'.\n(SELECT GroupName, String_Agg(Name, ', ') WITHIN GROUP ( ORDER BY Name)  AS departments\n  FROM AdventureWorks2016.HumanResources.Department\n  GROUP BY GroupName\n--and we then convert it to JSON\n  FOR JSON AUTO),\n-- here is your non-json query. This is an 'XML-trick' version to produce the list\n(SELECT GroupName,\n  (\n  SELECT\n    Stuff((\n    SELECT ', ' + Name\n      FROM AdventureWorks2016.HumanResources.Department AS dep\n      WHERE dep.GroupName = TheGroup.GroupName ORDER BY dep.name\n    FOR XML PATH(''), TYPE\n    ).value('.', 'varchar(max)'),\n  1,2,'')) AS departments\n  FROM AdventureWorks2016.HumanResources.Department AS thegroup\n  GROUP BY GroupName\n--and we then convert it to JSON\n  FOR JSON AUTO\n  )\n)\nWHERE SideIndicator <> '==' --meaning ALL the items that don't match\n--hopefully, nothing will get returned\n<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT * FROM dbo.Compare_JsonObject(--compares two JSON Documents\n-- here is your non-json query. This is just an example using 'string_agg'.\n(SELECT GroupName, String_Agg(Name, ', ') AS departments\n  FROM AdventureWorks2016.HumanResources.Department\n  GROUP BY GroupName\n--and we then convert it to JSON\n  FOR JSON AUTO),\n-- here is your non-json query. This is an 'XML-trick' version to produce the list\n(SELECT GroupName,\n  (\n  SELECT\n    Stuff((\n    SELECT ', ' + Name\n      FROM AdventureWorks2016.HumanResources.Department AS dep\n      WHERE dep.GroupName = TheGroup.GroupName\n    FOR XML PATH(''), TYPE\n    ).value('.', 'varchar(max)'),\n  1,2,'')) AS departments\n  FROM AdventureWorks2016.HumanResources.Department AS thegroup\n  GROUP BY GroupName\n--and we then convert it to JSON\n  FOR JSON AUTO\n  )\n)\nWHERE SideIndicator <> '==' --meaning ALL the items that don't match\n<\/pre>\n\n\n\n
which gives two mismatches, produced by different orders of the list items<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We can see what doesn\u2019t match, and we can see why: the ordering for those two rows is wrong.<\/p>\n\n\n\n
Second Example<\/h1>\n\n\n\n
We could, of course, use variables to store our JSON:<\/p>\n\n\n\n
DECLARE @SourceJSON NVARCHAR(MAX) = '{\n\n  \"question\": \"What is a clustered index?\",\n  \"options\": [\n    \"A bridal cup used in marriage ceremonies by the Navajo indians\",\n    \"a bearing in a gearbox used to facilitate double-declutching\",\n    \"An index that sorts and store the data rows in the table or view based on the key values\"\n  ],\n  \"answer\": 3\n}',\n@TargetJSON NVARCHAR(MAX) = '{\n\n  \"question\": \"What is a clustered index?\",\n  \"options\": [\n\t\"a form of mortal combat referred to as ''the noble art of defense''\",\n    \"a bearing in a gearbox used to facilitate double-declutching\",\n\t\"A bridal cup used in marriage ceremonies by the Navajo indians\",\n    \"An index that sorts and store the data rows in the table or view based on the key values\"\n\n  ],\n  \"answer\": 4\n}'\nSELECT SideIndicator, ThePath, TheKey, TheSourceValue, TheTargetValue\n  FROM dbo.Compare_JsonObject(@SourceJSON, @TargetJSON) AS Diff;\n<\/pre>\n\n\n\n
…which gives us all the differences:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
That side-indicator column is telling us things. A match is \u2018==\u2019, a difference is \u2018<>\u2019, a missing property in the source is \u2018->\u2019 and a missing property in the target is \u2018<-\u2018<\/p>\n\n\n\n
The \u2018path\u2019 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.<\/p>\n\n\n\n
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<\/p>\n\n\n\n
SELECT ThePath, TheKey, TheSourceValue, TheTargetValue,\n  Json_Value(@SourceJSON, TheParent + '.question') AS TheQuestion\n  FROM dbo.Compare_JsonObject(@SourceJSON, @TargetJSON)\n  WHERE SideIndicator = '<>';<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The Compare_JsonObject function<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
CREATE OR ALTER FUNCTION dbo.Compare_JsonObject (@SourceJSON NVARCHAR(MAX), @TargetJSON NVARCHAR(MAX))\n\/**\nSummary: >\n  This function 'diffs' a source JSON document with a target JSON document and produces an\n  analysis of which properties are missing in either the source or target, or the values\n  of these properties that are different. It reports on the properties and values for \n  both source and target as well as the path that references that scalar value. The \n  path reference to the object's parent is exposed in the result to enable a query to\n  reference the value of any other object in the parent that is needed. \nAuthor: Phil Factor\nDate: 06\/07\/2020\nRevised:\n\t- mod: Added the parent reference to the difference report\n\t- Date: 09\/07\/2020\nDatabase: PhilsRoutines\nExamples:\n   - SELECT * FROM dbo.Compare_JsonObject(@TheSourceJSON, @TheTargetJSON)\n       WHERE SideIndicator <> '==';\n   - SELECT *, Json_Value(@TheSourceJSON,TheParent+'.name')\n       FROM dbo.Compare_JsonObject(@TheSourceJSON, @TheTargetJSON)\n       WHERE SideIndicator <> '==';\nReturns: >\n  SideIndicator:  ( == equal, <- not in target, ->  not in source, <> not equal\n  ThePath:   the JSON path used by the SQL JSON functions \n  TheKey:  the key field without the path\n  TheSourceValue: the value IN the SOURCE JSON document\n  TheTargetValue: the value IN the TARGET JSON document\n\n**\/\nRETURNS @returntable TABLE\n  (\n  SideIndicator CHAR(2), -- == means equal, <- means not in target, -> means not in source, <> means not equal\n  TheParent  NVARCHAR(2000), --the parent object\n  ThePath NVARCHAR(2000), -- the JSON path used by the SQL JSON functions \n  TheKey NVARCHAR(200), --the key field without the path\n  TheSourceValue NVARCHAR(200), -- the value IN the SOURCE JSON document\n  TheTargetValue NVARCHAR(200) -- the value IN the TARGET JSON document\n  )\nAS\n  BEGIN\n    IF (IsJson(@SourceJSON) = 1 AND IsJson(@TargetJSON) = 1) --don't try anything if either json is invalid\n      BEGIN\n        DECLARE @map TABLE --these contain all properties or array elements with scalar values\n          (\n          iteration INT, --the number of times that more arrays or objects were found\n          SourceOrTarget CHAR(1), --is this the source 's' OR the target 't'\n \t\t  TheParent NVARCHAR(80), --the parent object\n\t\t  ThePath NVARCHAR(80), -- the JSON path to the key\/value pair or array element\n          TheKey NVARCHAR(2000), --the key to the property\n          TheValue NVARCHAR(MAX),-- the value\n          TheType INT --the type of value it is\n          );\n        DECLARE @objects TABLE --this contains all the properties with arrays and objects \n          (\n          iteration INT,\n          SourceOrTarget CHAR(1),\n\t\t  TheParent NVARCHAR(80),\n          ThePath NVARCHAR(80),\n          TheKey NVARCHAR(2000),\n          TheValue NVARCHAR(MAX),\n          TheType INT\n          );\n        DECLARE @depth INT = 1; --we start in shallow water\n        DECLARE @HowManyObjectsNext INT = 1, @SourceType INT, @TargetType INT;\n        SELECT --firstly, we try to work out if the source is an array or object\n          @SourceType = \n            CASE IsNumeric((SELECT TOP 1 [key] FROM OpenJson(@SourceJSON))) \n              WHEN 1 THEN 4 ELSE 5 END,\n          @TargetType= --and if the target is an array or object\n            CASE IsNumeric((SELECT TOP 1 [key] FROM OpenJson(@TargetJSON))) \n              WHEN 1 THEN 4 ELSE 5 END\n        --now we insert the base objects or arrays into the object table      \n        INSERT INTO @objects \n          (iteration, SourceOrTarget, TheParent, ThePath, TheKey, TheValue, TheType)\n          SELECT 0, 's' AS SourceOrTarget,'' AS parent, '<\/pre>\n\n\n\n
Now, this is all I need but it isn\u2019t the end to it. What if you just want to test part of a JSON document against another? That is a feature that isn\u2019t too complicated to add. What if you didn\u2019t 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.<\/p>\n\n\n\n
--source\n[\n\"Monday\",\n\"Tuesday\",\n\"Wednesday\",\n\"Thursday\",\n\"Friday\"\n]\n--target\n[\n\"Tuesday\",\n\"Wednesday\",\n\"Thursday\",\n\"Friday\",\n\"Monday\"\n]<\/pre>\n\n\n\n
You may want a \u2018lax\u2019 mode that matched elements in an array regardless of order. It sounds easy, but the references won\u2019t 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\u2019ve 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\u2019t crop up so much in SQL Server, because the implementation of JSON can\u2019t easily produce them, and this function is sufficient for my unit-testing needs.<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong>
How to consume hierarchical JSON in SQL Server using OPENJSON<\/code><\/a> <\/p>\n\n<\/div>\n<\/div> \n\n\n
Conclusions<\/h2>\n\n\n\n
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\u2019m beginning to wonder if we have a way of making automated database testing somewhat easier!<\/p>\n\n\n\n
AS path, '', @SourceJSON, @SourceType; INSERT INTO @objects (iteration, SourceOrTarget,TheParent, ThePath, TheKey, TheValue, TheType) SELECT 0, 't' AS SourceOrTarget, '' AS parent, '<\/pre>\n\n\n\n
Now, this is all I need but it isn\u2019t the end to it. What if you just want to test part of a JSON document against another? That is a feature that isn\u2019t too complicated to add. What if you didn\u2019t 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.<\/p>\n\n\n\n
<\/pre>\n\n\n\n
You may want a \u2018lax\u2019 mode that matched elements in an array regardless of order. It sounds easy, but the references won\u2019t 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\u2019ve 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\u2019t crop up so much in SQL Server, because the implementation of JSON can\u2019t easily produce them, and this function is sufficient for my unit-testing needs.<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong>
How to produce JSON documents from SQL Server queries with FOR JSON<\/code><\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
\n    
FAQs: SQL Server JSON Diff. Checking for differences between JSON documents.<\/h2>\n\n                        
1. How do I compare two JSON documents in SQL Server?<\/h3>\n            
\n                
Use the Compare_JsonObject table-valued function described in this article. Pass two JSON strings as parameters and the function returns a result set where each row represents a property path with an indicator column showing the comparison result: == (match), <> (different values), -> (missing from source), or <- (missing from target). The function handles nested JSON hierarchies recursively.<\/p>\n            <\/div>\n                    
2. Does SQL Server have a built-in JSON diff function?<\/h3>\n            
\n                
No. SQL Server does not have a native function for comparing two JSON documents. You can compare individual JSON property values using JSON_VALUE() and OPENJSON(), but a full structural diff requires a custom implementation. The Compare_JsonObject function presented in this article provides that capability as a deployable table-valued function.<\/p>\n            <\/div>\n                    
3. How do I validate JSON output from a stored procedure in SQL Server?<\/h3>\n            
\n                
Use the Compare_JsonObject function to compare the procedure’s JSON output against a known-good reference document. Call the function with the expected JSON as one parameter and the actual output as the other. If the function returns no rows, the documents are structurally identical. Any rows returned indicate differences – use the indicator column to identify what changed.<\/p>\n            <\/div>\n                    
4. Can I compare partial JSON objects in SQL Server?<\/h3>\n            
\n                
Yes. You can pass a subset of a larger JSON document to Compare_JsonObject by first extracting the relevant path using JSON_QUERY(). This lets you validate specific sections of a large JSON response without comparing the entire document, which is useful when testing APIs or stored procedures that return large payloads.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Compare two JSON documents in SQL Server using a T-SQL table-valued function that returns row-by-row differences, matches, and missing properties – no external tools required. Includes two worked examples with AdventureWorks data.…<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-87545","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=87545"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87545\/revisions"}],"predecessor-version":[{"id":110023,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/87545\/revisions\/110023"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=87545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=87545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=87545"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=87545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}