Exploring SQL Server 2016 – JSON data – Part 1

Now-a-days JSON (JavaScript Object Notation) data format has become popular in terms of exchanging data to different data sources. It is the primary format in storing data for NoSQL databases like Azure DocumentDB, MongoDB, etc. It is easily in readable format and can store unstructured data as well.

You can parse and generate data in JSON format in SQL Server from the version 2016 onwards. In this post we will explore how to generate data in simple JSON format.

Let us create the following dataset

create table #emp(emp_name varchar(100), dob date)
insert into #emp(emp_name,dob)
select 'Madhivanan','2000-10-19' union all
select 'Saran','1998-04-22' union all
select 'Mugil','1978-02-14'

Now you can use JSON AUTO option to generate data in json format as show below


select * from #emp for json auto

The result is


JSON_F52E2B61-18A1-11d1-B105-00805F49916B
[{
	"emp_name": "Madhivanan",
	"dob": "2000-10-19"
}, {
	"emp_name": "Saran",
	"dob": "1998-04-22"
}, {
	"emp_name": "Mugil",
	"dob": "1978-02-14"
}]

As you can see, each column is by default separated by colon and each row is separated by comma and all values are within double quotes.

If you want to add single top level element on the top say “Employee”, that can be done using ROOT option as shown below


select * from #emp for json auto, root('Employee')

The result is


JSON_F52E2B61-18A1-11d1-B105-00805F49916B
{
	"Employee": [{
		"emp_name": "Madhivanan",
		"dob": "2000-10-19"
	}, {
		"emp_name": "Saran",
		"dob": "1998-04-22"
	}, {
		"emp_name": "Mugil",
		"dob": "1978-02-14"
	}]
}

If you want to have a column alias you can do like below

select
         (select * from #emp for json auto, root('Employee'))
as test_json

The result is


test_json
{
	"Employee": [{
		"emp_name": "Madhivanan",
		"dob": "2000-10-19"
	}, {
		"emp_name": "Saran",
		"dob": "1998-04-22"
	}, {
		"emp_name": "Mugil",
		"dob": "1978-02-14"
	}]
}
Advertisements

3 thoughts on “Exploring SQL Server 2016 – JSON data – Part 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s