Reading JSON data in T-SQL on SQL Server

Extracting values from JSON string on SQL Server using T-SQL

JSON is the most commonly used data fromat nowdays. It is lighter than XML, more readable and it's sindax complies with most OOP C-Like lnguages such as Java, JavaScript and C#.

There is not really many downsides of using JSON, so that is one of the rasons everyone is using it in one way or another.

Whan it comes to relational database it is a bit different. MIcrosoft SQL server does not use JSON for any data structure and by nature does not understand it. Starting from SQL Server 2016t, Microsoft introduced JSON support by adding OPENJSON function.

OPENJSON function basically transforms JSON document into collection of row-sets, in simple workds output of the function id table representation of JSON data.

DECLARE @json NVARCHAR(4000) = N'{  
   "FirstName":"John",  
   "LastName":"Smith",  
   "Age": 32,
   "DOB": "1986-10-07",
   "Married": false
}'

SELECT * FROM OPENJSON(@json)
    

Simple OPENJSON call wothout parameters will produce the output with key/value pair table with additional column of data type of the data read from JSON document.

key value type
FirstName John 1
LastName Smith 1
Age 32 2
DOB 1986-10-07 1
Married false 3

If you want to format table to get properties as columns which is most usefull way to use table for joining with other data tables, you can explicitly conver tot he type and column name using options

SELECT * FROM OPENJSON(@json) WITH (
	FirstName NVARCHAR(50) '$.FirstName',
	LastName NVARCHAR(100) '$.LastName',
	Age int '$.Age',
	DOB date '$.DOB',
	Married bit '$.Married'
)
    

 

FirstName LastName Age DOB Married
John Smith 32 1986-10-07 0

Another problem you might face is mutli-dimensional structure of JSON.

DECLARE @json NVARCHAR(4000) = N'{  
   "FirstName":"John",  
   "LastName":"Smith",  
   "Age": 32,
   "DOB": "1986-10-07",
   "Married": false,
   "Residence": {
		"Country":"Spain",
		"Region":"Andalusia",
		"City":"Seville"
   }
}'

SELECT * FROM OPENJSON(@json) WITH (
	FirstName NVARCHAR(50) '$.FirstName',
	LastName NVARCHAR(100) '$.LastName',
	Age int '$.Age',
	DOB date '$.DOB',
	Married bit '$.Married',
	Residence VARCHAR(200) '$.Residence'
)
    

You can see that embeded type of Residence property is null in output.

Flat structure is easy to read because it has two dimensions like data table, but if you introduce for example embeded objects as a value of a property, you have to change a query a little bit to adapt it to the JSON structure.

SELECT * FROM OPENJSON(@json) WITH (
	FirstName NVARCHAR(50) '$.FirstName',
	LastName NVARCHAR(100) '$.LastName',
	Age int '$.Age',
	DOB date '$.DOB',
	Married bit '$.Married',
	ResidenceCountry VARCHAR(200) '$.Residence.Country',
	ResidenceRegion VARCHAR(200) '$.Residence.Region',
	ResidenceCity VARCHAR(200) '$.Residence.City'
)
    

 

FirstName LastName Age DOB Married ResidenceCountry ResidenceRegion ResidenceCity
John Smith 32 1986-10-07 False Spain Andalusia Seville

 

 

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

.NET

read more

JavaScript

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article