SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




SQL Server 2019 Installation
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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.

query JSON data using SQL OpenJSON function

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.

SQL Server OpenJSON query sample

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.

SQL query JSON data using OpenJSON TVF

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.

split JSON string with multiple objects into single object SQL rows

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

SQL Server OpenJSON query output

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

pivot JSON data created with OpenJSON query

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

SQL Pivot query output using OpenJSON







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems