SQL Server 2016: JSON, Part 5 – Storage and Indexation

Total: 4 Average: 3.5

In this article, we will explore how we can store and index JSON objects in a database.

As we have already discussed, developers did not add a separate type for JSON to SQL Server 2016 as they did for XML.

Thus, you can use any string data types to store JSON. VARCHAR(MAX) and NVARCHAR(MAX) are more suitable depending on whether there are Unicode symbols in JSON files. However, you can use “smaller” data types if you are sure that your JSON objects will fit those datatypes correctly.

If you worked with XML, then you should remember that SQL Server provides several types of indexes, which help to reduce the selection time. However, there are no such indexes for string data types that are supposed to store JSON. As a workaround, we can use calculated columns. They can represent particular properties of JSON files by which we are going to search. We can create indexes for these columns.

use test;
go
 
drop table if exists dbo.test_table;
go
 
create table dbo.test_table (
	id int not null,
	json_data varchar(max) null,
 
	constraint pk_test_table primary key clustered (id)
);
go
 
insert into dbo.test_table (
	id,
	json_data
)
values
	(1, '{"first name":"Sergey","last name":"Olontsev","age":32,"skills":
        ["SQL Server 2016","T-SQL","JSON"]}'),
	(2, '{"first name":"John","last name":"Smith","sex":"m","skills":
        ["SQL Server 2014","In-Memory OLTP"]}'),
	(3, '{"first name":"Mary","last name":"Brown","age":25,"skills":
        ["SQL Server 2016","In-Memory OLTP"]}');
go
 
alter table dbo.test_table
add v_age as json_value(json_data, '$.age') persisted;
go
 
alter table dbo.test_table
add v_skills as json_query(json_data, '$.skills') persisted;
go
 
 
create nonclustered index ix_nc_test_table_v_age on [dbo].[test_table] (v_age);
go
 
create fulltext catalog [jsonFullTextCatalog] with accent_sensitivity = 
on authorization [dbo];
go
 
create fulltext index on [dbo].[test_table] (v_skills)
key index pk_test_table ON jsonFullTextCatalog;
go
select *
from [dbo].[test_table] as t
where
	t.[v_age] = 32;
 
select *
from [dbo].[test_table] as t
where
	contains(t.v_skills, 'OLTP');

I would recommend you to create calculated columns as persisted. Otherwise, there is no sense to add indexes to those columns. Additionally, you can create both standard and full-text indexes if you want to get a flexible search by the array content or by the objects. In addition, full-text indexes have no special rules for processing JSON. They simply split the text into separate words using double quotes, commas, brackets, etc. as unit separators.

In the next article, we will compare the procedures of storing and processing JSON and XML in SQL Server. We will try to find the best format to store and transmit unstructured data.

Sergey Olontsev