JSONTABLE – Shredding JSON data into a relational structure

Even though support for XML data has been in the product for a long time now (introduced in SQL 2000 and then improved in SQL 2005), whenever I’m working with it I still find it completely unintuitive and constantly have to refer back to the documentation and previously worked examples.

Most of all I rely heavily on an amazing snippet of code developed by Jacob Sebastian that parses out all the elements of XML into an easily understandable table. The code is encapsulated into the XMLTABLE function.

The original link to this and the entire website that hosted it is now long gone. However the code seems to still live on for now at https://gist.github.com/jacobvettickal/601c197d716d0aeb40c1c177ca1503d8

Fast forward a number of years and we now have support for JSON data too (since SQL 2016) and I recently found myself in a familiar place with some complex JSON data, wanting to quickly work out the correct path and it’s syntax to pass into JSON_QUERY() and JSON_VALUE() functions.

I wished for JSON equivalent of the XMLTABLE function, but my internet searches proved fruitless. So what’s the next best thing to do – that’s right – write it yourself! Using the structure of XMLTABLE as a template (thanks Jacob) I worked through the code and produced a JSON specific version. Clearly there are differences. XML boils down to elements and attributes, JSON is key-value pairs and arrays of objects. Nevertheless the relational output is similar and the resultant JSON path expressions can be used in the SQL server supported JSON functions.

I’ll maintain the code at https://github.com/kevriley/JSONTable

Usage is simple, just pass in the JSON data as NVARCHAR, just as all the other in-built functions do.

declare @myjson nvarchar(4000) = N' 
{
    "glossary": {
        "title": "example glossary",
	"GlossDiv": {
            "title": "S",
		"GlossList": {
                "GlossEntry": {
                  "ID": "SGML",
		  "SortAs": "SGML",
		  "GlossTerm": "Standard Generalized Markup Language",
		  "Acronym": "SGML",
		  "Abbrev": "ISO 8879:1986",
		  "GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
				"GlossSeeAlso": ["GML", "XML"]
                    },
		"GlossSee": "markup"
                }
            }
        }
    }
}
'
select * from JSONTABLE(@myjson)

I’ve tested it against example JSON data from https://json.org/example.html and real-world JSON data from systems that I currently work on, but as ever there’s likely something I’ve missed. That’s where you come in dear reader. If you have any JSON data that this is not shredding correctly I’d love to know. You can find my contact details on this blog or from Github.

Aside from that it’s free for all to use, let me know if you find it useful.