By: Aaron Bertrand | Updated: 2015-10-30 | Comments (1) | Related: 1 | 2 | 3 | > SQL Server 2016
Problem
In previous tips, Introducing JSON for SQL Server 2016 and JSON Support in SQL Server 2016, you learned about the functionality available in CTP2: translating relational data into JSON string output, using FOR JSON AUTO and FOR JSON PATH. However, much more functionality is coming.
Solution
In CTP3, more elaborate functionality will be available, including the ability to translate JSON text into relational data, extract scalar values from JSON strings, and validate the format of JSON input. In this tip, I'll demonstrate these three features briefly.
OPENJSON()
This rowset function will return relationally structured data from JSON input; the opposite of FOR JSON AUTO/PATH. A simple example:
DECLARE @json NVARCHAR(4000) = N'{
"UserID" : 1,
"UserName": "AaronBertrand",
"Active": true,
"SignupDate": "2015-10-01"
}';
SELECT * FROM OPENJSON(@json);The output in this case is a rather generic list of key-value pairs, similar to the input:
key value type ---------- ------------- ---- UserID 1 2 UserName AaronBertrand 1 Active true 3 SignupDate 2015-10-01 1
However, you can also define more specific output structure using the WITH clause, for example (and adding a second row):
DECLARE @json NVARCHAR(4000) = N'[{
"UserID" : 1,
"UserName": "AaronBertrand",
"Active": true,
"SignupDate": "2015-10-01"
},
{
"UserID" : 2,
"UserName": "BobO''Neil",
"Active": false,
"SignupDate": "2014-12-13"
}]';
SELECT * FROM OPENJSON(@json)
WITH
(
UserID INT,
UserName NVARCHAR(64),
Active BIT,
[Started] DATETIME '$.SignupDate' -- remap column name
);The results are now a bit more relational:
UserID UserName Active Started ------ ------------- ------ ----------------------- 1 AaronBertrand 1 2015-10-01 00:00:00.000 2 BobO'Neil 0 2014-12-13 00:00:00.000
Another interesting use case for OPENJSON() is to facilitate string splitting. In current versions of SQL Server, people typically resort to user-defined functions, CLR, or table-valued parameters . Watch how easy this work becomes with OPENJSON():
CREATE PROCEDURE dbo.ShowObjects
@list NVARCHAR(4000)
AS
BEGIN
SET NOCOUNT ON;
SELECT name
FROM sys.all_objects
WHERE [object_id] IN
(
SELECT CONVERT(INT, value)
FROM OPENJSON(N'[' + @list + N']')
);
END
GO
EXEC dbo.ShowObjects @list = N'-101,-102,-103';Results:
all_columns all_views all_objects
You can see more elaborate examples of using OPENJSON() in these blog posts from Microsoft's Jovan Popovic: OPENJSON - The easiest way to import JSON text into table and JSON Support in SQL Server 2016.
JSON_VALUE()
This function will extract specific scalar values from within the JSON text. As you can imagine, JSON strings can get pretty complex, so there is a set of syntax for determining exactly where in the hierarchy to extract from. A few simple examples:
DECLARE @json NVARCHAR(4000) = N'{
"UserID" : 1,
"Cars": [
{ "Year":2014, "Make":"Jeep", "Model":"Grand Cherokee" },
{ "Year":2010, "Make":"Nissan", "Model":"Murano", "Options":
[{ "AC":true,"Panoramic Roof":true }]
]
}';
SELECT
UserID = JSON_VALUE(@json, '$.UserID'),
Model1 = JSON_VALUE(@json, '$.Cars[0].Model'),
Model2 = JSON_VALUE(@json, '$.Cars[1].Model'),
Has_AC = JSON_VALUE(@json, '$.Cars[1].Options[0].AC');Results:
UserID Model1 Model2 Has_AC ------ -------------- ------ ------ 1 Grand Cherokee Murano true
ISJSON()
This function returns a bit value: 1 if the input is a valid JSON document; 0 if not. On its own, the usage is quite simple:
DECLARE @json NVARCHAR(4000) = N'[{
"UserID" : 1,
"UserName": "AaronBertrand",
"Active": true,
"SignupDate": "2015-10-01"
}]';
SELECT ISJSON(@json), -- returns 1
ISJSON(N'foo'); -- returns 0The value here is not so much to interrogate individual values, but rather to define things like validation logic in triggers and check constraints. For example, if I have a table where I am storing JSON text, I may want a constraint to prevent any garbage from getting in there (keep in mind that there is no JSON type; the existing NVARCHAR type is used).
CREATE TABLE dbo.JSONExample ( UserID INT PRIMARY KEY, Attributes NVARCHAR(4000), CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes)) = 1 );
Now, if I try to insert garbage into this column:
INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'foo';
I get the following error message:
Msg 547, Level 16, State 0 The INSERT statement conflicted with the CHECK constraint "No Garbage". The conflict occurred in database "tempdb", table "dbo.JSONExample", column 'Attributes'. The statement has been terminated.
However this will work fine:
INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'{"garbage": false}';Summary
The introduction of JSON functionality to SQL Server will allow for greater compatibility with existing applications and fewer barriers and cumbersome workarounds to sharing data between systems. In forthcoming related tips, I will talk in more detail about practical use cases for some of these features, and demonstrate the conversion of an EAV structure to one that uses JSON for storage instead.
Next Steps
- Download SQL Server 2016 CTP3 when it becomes available (or register for a trial of Azure SQL Database, where this feature will also be available).
- Make sure your database compatibility level is 130; during the CTP period at least, JSON features may not function under older compatibility levels.
- Try out JSON in scenarios where it may seem useful.
- See these related tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-10-30
