SQL Server 2016: JSON, Part 2 – Data Generation

Total: 2 Average: 4.5

In my previous publication, we have explored how you can transfer data from JSON to SQL Server 2016. Now, I am going to describe how you can generate JSON from relational data. You can do this using the FOR JSON statement.

The easiest way is to use FOR JSON AUTO. In this case, it will generate the JSON object array where each row in the selection will be a separate object, while a column will be a property.

use tempdb;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	name varchar(100) null,
	dt datetime null
);
go
 
insert into dbo.test_table (id, name, dt)
values
	(1, 'qwe', '19000101'),
	(2, 'asd', null),
	(3, null, '20000101');
go
 
select id, name, dt
from dbo.test_table
for json auto;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

As you can see, NULL values are being ignored. If we want to include them into JSON, we can use the additional INCLUDE_NULL_VALUES option.

select id, name, dt
from dbo.test_table
for json auto, include_null_values;
go

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”,”dt”:null},

{“id”:3,”name”:null,”dt”:”2000-01-01T00:00:00″}]

The WITHOUT_ARRAY_WRAPPER statement may help if we want to display JSON without square brackets.

select id, name, dt
from dbo.test_table
for json auto, without_array_wrapper;
go

{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}

If we need to combine the results with the root component, then it is better to use the ROOT statement and specify its proper name.

select id, name, dt
from dbo.test_table
for json auto, root('root');
go

{“root”:

[{“id”:1,”name”:”qwe”,”dt”:”1900-01-01T00:00:00″},

{“id”:2,”name”:”asd”},

{“id”:3,”dt”:”2000-01-01T00:00:00″}]

}

Finally, if you want to create JSON with a more complex structure, specify proper names for properties and group them, then you should use the FOR JSON PATH statement. I recommend that you should refer to the first article about JSON to learn more about the path statements for JSON.

To check how you can generate JSON with a more complex structure, run this query:

select
	id,
	name as 'data.full_name',
	dt as 'data.add date'
from dbo.test_table
for json path;
go

[{“id”:1,”data”:{“full_name”:”qwe”,”add date”:”1900-01-01T00:00:00″}},

{“id”:2,”data”:{“full_name”:”asd”}},

{“id”:3,”data”:{“add date”:”2000-01-01T00:00:00″}}]

In my further publication, we will explore how you can convert the JSON object into a relational data set.

 

Useful tool:

dbForge Data Generator for SQL Server – powerful GUI tool for a fast generation of meaningful test data for databases.

Sergey Olontsev