In SQL Server version 2016, we have reasonably good techniques to both create and parse (shred) JSON. The techniques outlined have so far proven to be quite efficient, too: for example, we use JSON quite a bit when dealing with Qualtrics APIs, and those API returns can be quite large, but the SQL to shred them is surprisingly performant.
The most useful function to use when exploring a JSON string in the SQL environment is OPENJSON(). It is a table-valued function that parses JSON text and returns objects and properties as rows and columns. Like other table-valued functions, it is easy to use with OUTER APPLY as below:
-- create a test Json string
drop table if exists #Json
select cast('
{ "store": {
"book": [
{ "category": "reference",
"author": "Nigel Rees",
"title": "Sayings of the Century",
"price": 8.95
},
{ "category": "fiction",
"author": "Evelyn Waugh",
"title": "Sword of Honour",
"price": 12.99
},
{ "category": "fiction",
"author": "Herman Melville",
"title": "Moby Dick",
"isbn": "0-553-21311-3",
"price": 8.99
},
{ "category": "fiction",
"author": "J. R. R. Tolkien",
"title": "The Lord of the Rings",
"isbn": "0-395-19395-8",
"price": 22.99
}
],
"bicycle": {
"color": "red",
"price": 19.95
}
}
}' as nvarchar(max)) as JsonText
into #Json
select jt.*
from #Json j
outer apply openjson(j.JsonText) jt
As used above, OPENJSON returns a dataset that looks like this:
We can specify certain pieces of a JSON document precisely by adding a JSONPath statement to our query. The query below returns all properties associated with the *first* book object in our JSON document by specifying store.book[0] where the [0] refers to a numbered element in an array.
select jt.*
from #Json j
outer apply openjson(j.JsonText, '$.store.book[0]') jt
For more information on JSONPath syntax, this Microsoft article is helpful: https://docs.microsoft.com/en-us/sql/relational-databases/json/json-path-expressions-sql-server?view=sql-server-ver15
Unfortunately, the JSONPath implementation in SQL Server 2016 is *very* limited. Other tool sets allow for the use of wildcards like * when navigating arrays, [,] syntax to return multiple node sets, etc. This article spells out some of the functionality that is not yet available but might be in future versions of SQL Server: https://goessner.net/articles/JsonPath/
One other useful approach is to combine OPENJSON, a WITH clause, and JSONPath syntax to drill into various levels of the JSON document at one time:
select jt.*
from #Json j
outer apply openjson(j.JsonText)
with (
Store nvarchar(max) '$.store' as json -- returns all items in the "store"
, Books nvarchar(max) '$.store.book' as json -- returns all books
, Book1 nvarchar(max) '$.store.book[0]' as json -- returns the first book in the JSON array
, Book1Price decimal(5,2) '$.store.book[0].price' -- returns the price of the first book
) jt