Query JSON Data using OpenJSON on SQL Server
With SQL Server 2016 and SQL Server 2017, database programmers can query JSON data in their SQL codes using build-in OpenJSON SQL table valued function. In this Transact-SQL tutorial, I'ld like to share basic examples for OPENJSON SQL queries in combination with OPENJSON TVF using WITH clause where output json column values can be defined.
In fact, with increasing usage of JSON especially in web applications or in mobile apps, each passing day database developers require methods to query JSON data using SQL more and more. I hope this OpenJson tutorial with given samples enables SQL Server database programmers a fast start with querying JSON strings.
Let's start with a simple query.
Assume that you have a JSON string which includes basic information about a single country including its code, name and capital city name.
For example, following JSON string is storing country details for Turkey.
{
"code": "TR",
"name": "Türkiye",
"capital": "Ankara"
}
As seen above, the key-value pairs are listed following each other with one-to-one relation.
Now as a SQL developer, I want to convert above JSON string into a tabular format with code, name and capital are the column names of the query output.
declare @Country nvarchar(max) = '{"code":"TR","name":"Türkiye","capital":"Ankara"}'
select * from OpenJson(@country, '$') as country
As in below screenshot showing the output result of the OpenJSON table-valued function, database programmers managed to fetch each key and its corresponding value as a list.
Actually, I am not interested in TYPE column but more on key and value column values.
But I think it will be more understandable at least for my data model if I could see all these distinct properties of the country in a single row.
I think SQL developers too will find it more readable if data is converted into a single row data using following OpenJSON query using WITH clause
declare @Country nvarchar(max) = '{"code":"TR","name":"Türkiye","capital":"Ankara"}'
select *
from OpenJson(@country, '$') with (
code varchar(5) '$.code',
[name] nvarchar(100) '$.name',
capital nvarchar(100) '$.capital'
) as country
As seen in the query output, now instead of displaying each JSON property in a seperate line, using the WITH clause settings each JSON property is converted into a column data.
In OpenJSON WITH clause the column names are defined with their SQL data type definitions.
Additionally, we have mapped the JSON query value for that recently defined SQL column data.
What happens if I have multiple country data in JSON string. Here is an example JSON data including data for 3 countries
declare @CountryList nvarchar(max) = '[
{"code":"TR","name":"Türkiye","capital":"Ankara"},
{"code":"US","name":"United States","capital":"Washington"},
{"code":"DE","name":"Deutschland","capital":"Berlin"}
]'
Let's execute the SQL SELECT query with OpenJSON table function which has WITH clause as follows
declare @CountryList nvarchar(max) = '[
{"code":"TR","name":"Türkiye","capital":"Ankara"},
{"code":"US","name":"United States","capital":"Washington"},
{"code":"DE","name":"Deutschland","capital":"Berlin"}
]'
select *
from OpenJson(@CountryList, '$') with (
code varchar(5) '$.code',
[name] nvarchar(100) '$.name',
capital nvarchar(100) '$.capital'
) as country
See below output? There is no need to modify our SQL query if our JSON string includes data for more than single object.
Another solution I want to introduce you is actually a little bir more longer and unnecessary especially after you understand how above SQL solution works.
Let's execute following SQL scripts with OpenJSON function and talk on results
declare @CountryList nvarchar(max) = '[
{"code":"TR","name":"Türkiye","capital":"Ankara"},
{"code":"US","name":"United States","capital":"Washington"},
{"code":"DE","name":"Deutschland","capital":"Berlin"}
]'
select * from openjson(@CountryList, '$')
As seen below, in the query output we have each JSON object in a seperate row in JSON format. So we have simply splitted JSON string into objects it included so that each object is represented again in JSON format.
At this point, SQL database developer can use CROSS APPLY in combination with OpenJSON to split each single JSON object into its key-value pairs as follows.
Actually what we do is repeating the first solution using CROSS APPLY
declare @CountryList nvarchar(max) = '[
{"code":"TR","name":"Türkiye","capital":"Ankara"},
{"code":"US","name":"United States","capital":"Washington"},
{"code":"DE","name":"Deutschland","capital":"Berlin"}
]'
select
list.[key] as [list key], list.[value] as countryJSON, list.[type],
c.[key] as [country property], c.[value] as [property value], c.[type]
from openjson(@CountryList, '$') as list
cross apply openjson (list.[value], '$') as c
This output rows can be pivotted into a more meaningful result set using CASE statements for each desired output column and a key column identifying each single country in combination with a GROUP BY clause.
Please check following SQL SELECT query where row data is converted into column data for each country property based on that related country.
select
max([country code]) as [country code],
max([country name]) as [country name]
from (
select
list.[key] as [list key],
case when c.[key] = 'code' then c.[value] end as [country code],
case when c.[key] = 'name' then c.[value] end as [country name]
from openjson(@CountryList, '$') as list
cross apply openjson (list.[value], '$') as c
) cList
group by [list key]
order by [list key]
The query output is now more readable and contains all properties transferred via JSON string for each country in its output row
Maybe if a SQL database developer decides to PIVOT OpenJSON query data, it is better to use the SQL Pivot command as follows
select
[code],[name],[capital]
from (
select
list.[key] as [list key],
c.[key] as [country property], c.[value] as [property value]
from openjson(@CountryList, '$') as list
cross apply openjson (list.[value], '$') as c
) DataTable
PIVOT
(
MAX([property value])
For [country property]
IN (
[code],[name],[capital]
)
) PivotTable
In fact the output of the SQL Pivot query is exactly the same as all above solutions
