What's new in SQL Server 2016: JSON

This post is part of the series 'Microsoft SQL Server 2016'. Be sure to check out the rest of the blog posts of the series!

• What's new in SQL Server 2016: JSON (this post)
• Comparing execution plans with SQL Server
• What's new in SQL Server 2016: Dynamic Data Masking
• What's new in SQL Server 2016: Row-Level Security
• What's new in SQL Server 2016: Stretch Database
• What's new in SQL Server 2016: Live Query Statistics
• What's new in SQL Server 2016: Temporal Tables
• What's new in SQL Server 2016: Always encrypted
• SQL Server 2016 SP1 - Licensing changes

The CTP3 of SQL Server 2016 has just been released. So it's time to take a look at the new features provided by this version. Today we will focus on JSON support.

#Creating a JSON Column

Unlike XML columns, JSON columns do not have a particular type. So we use the type nvarchar.

CREATE TABLE Sample_Json (
    [Id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [Column_Json] [nvarchar](500) NULL
)

To make sure that the column contains only JSON format strings we can add a constraint using the new ISJSON() function:

ALTER TABLE Sample_Json
ADD CONSTRAINT Json_Constraint CHECK (ISJSON(Column_Json) = 1)

To add a line, the syntax is always the same:

INSERT INTO Sample_Json(Column_Json)
VALUES ('{"FirstName":"Gérald", "LastName":"Barré", "Nickname":"Meziantou"}'),
       ('{"FirstName":"John", "LastName":"Doe", "PhoneNumbers": ["0123456789", "9876543210"]}')

#Using JSON in SQL Server

SQL Server provides several functions to use data in JSON format.

##JSON_VALUE function

JSON_VALUE retrieves a value from a string in JSON format:

SELECT JSON_VALUE(Column_Json, '$.FirstName') FROM Sample_Json
-- 'Gérald', 'John'

The syntax is as follows:

• $: Root element of the JSON document
• $.name or $."first name": Select a property of the element
• $.phoneNumbers[1]: Selects the 2nd element of the array

Of course you can use a combination of all possibilities: $.Friends[1].FirstName.

##JSON_QUERY function

JSON_QUERY retrieves an object or array from a JSON chain:

SELECT JSON_QUERY(Column_Json, '$.PhoneNumbers') FROM Sample_Json
-- NULL, '["0123456789", "9876543210"]'

##OPENJSON function

OPENJSON converted a JSON object to a table:

SELECT * FROM OPENJSON('{"Name":"John", "Age":20, "DateOfBirth": null, "Tags": ["Customer"], "PhoneNumbers": { "Home": "0123456789" }, "IsActive":true}')

You can see that SQL Server automatically adds a type column. This column corresponds to the actual type of data:

• 0: null
• 1: string
• 2: number
• 3: Boolean
• 4: array
• 5: object

You may want to open a JSON sub-element:

SELECT * FROM
OPENJSON (@json, '$.Tags')

It is also possible to define the schema of the created table:

DECLARE @json nvarchar(1024)
SET @json = '[{"Name": "Meziantou","DateOfBirth": "2000-11-11T00:00:00","OrderCount": 10},{"Name": "John","DateOfBirth": "1990-01-31T00:00:00","OrderCount": 2 }]'

SELECT * FROM OPENJSON(@json)
WITH (
    [Name] varchar(200) '$.Name' ,
    [DateOfBirth] datetime '$.DateOfBirth',
    [OrderCount] int '$.OrderCount'
)

The CROSS APPLY operator may also be useful for converting JSON data to columns in the row:

SELECT * FROM Sample_Json
CROSS APPLY OPENJSON (Column_Json)
WITH (
  FirstName nvarchar(50) '$.FirstName',
  LastName nvarchar(50) '$.LastName'
)

#Generate JSON

Just as it is possible to generate XML elements with the FOR XML clause, it is now possible to generate JSON with the FOR JSON clause:

INSERT INTO Customer (Name, DateOfBirth, IsActive) VALUES
('Meziantou', '2000-01-01 00:00:00', 1),
('John', NULL, 0)

SELECT * FROM Customer
FOR JSON AUTO
-- [{"Id":1,"Name":"Meziantou","DateOfBirth":"2000-01-01T00:00:00","IsActive":true},{"Id":2,"Name":"John","IsActive":false}]

By default, NULL values ​​are excluded. To include them, you must specify INCLUDE_NULL_VALUES:

SELECT * FROM Customer
FOR JSON AUTO, INCLUDE_NULL_VALUES

To create sub-objects you can use FOR JSON PATH. For this each . in the name of a column turns into a sub-object:

SELECT Id [Customer.Id], Name [Customer.Name], DateOfBirth [Customer.DateOfBirth] FROM Customer
FOR JSON PATH, ROOT('Customers'), INCLUDE_NULL_VALUES
-- {"Customers":[{"Customer":{"Id":1,"Name":"Meziantou","DateOfBirth":"2000-01-01T00:00:00"}},{"Customer":{"Id":2,"Name":"John","DateOfBirth":null}}]}

#JSON Constraints

When creating the table, we added a constraint on the JSON column. This constraint validates that the content is JSON. It is also possible to create more specific constraints. For example, to verify that the JSON object contains an age property of type number, we can use the function JSON_VALUE:

ALTER TABLE Sample_Json
ADD CONSTRAINT Age_Is_Number
CHECK (ISNUMERIC(JSON_VALUE(value, '$.age')) = 1)

You can also use JSON_QUERY:

ALTER TABLE Sample_Json
ADD CONSTRAINT Tags_Exist
CHECK (JSON_QUERY(value, '$.tags') IS NOT NULL)

#Creating an index

To create an index on one or more properties of the JSON document you have to add a computed column:

ALTER TABLE Sample_Json ADD FirstName AS JSON_VALUE(Column_Json, '$.FirstName')

CREATE INDEX idx_FirstName ON Sample_Json(FirstName)

You can then query the table using the calculated column or the JSON_VALUE function:

SELECT FirstName FROM Sample_Json
WHERE FirstName LIKE 'm%'

SELECT FirstName FROM Sample_Json
WHERE JSON_VALUE(Column_Json, '$.FirstName') LIKE 'm%'

SELECT JSON_VALUE(Column_Json, '$.FirstName') FROM Sample_Json
WHERE JSON_VALUE(Column_Json, '$.FirstName') LIKE 'm%'

In the 3 cases, the execution plan is identical and uses the index:

#Conclusion

SQL Server 2016 now lets you use data stored as JSON. This can be very handy, especially when you want to interact with other systems such as NoSQL databases that mainly use this format as storage.

Do you have a question or a suggestion about this post? Contact me!