Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server 2016 Advanced JSON Techniques - Part 1

By:   |   Updated: 2015-10-30   |   Comments (1)   |   Related: More > SQL Server 2016


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.


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.


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"


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"

   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():

  @list NVARCHAR(4000)

  SELECT name 
    FROM sys.all_objects
    WHERE [object_id] IN 
      SELECT CONVERT(INT, value)
        FROM OPENJSON(N'[' + @list + N']')

EXEC dbo.ShowObjects @list = N'-101,-102,-103';



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.


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 }]

  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');


UserID    Model1            Model2    Has_AC
------    --------------    ------    ------
1         Grand Cherokee    Murano    true


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 0

The 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).

  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}';


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

Last Updated: 2015-10-30

get scripts

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


Friday, February 23, 2018 - 2:03:33 AM - Sunil Nepali Back To Top


  Attributes NVARCHAR(4000),
  CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes)) = 1
In the query above there seems error. So Please find the right query for table creating.

CREATE TABLE dbo.JSONExample ( UserID INT PRIMARY KEY, Attributes NVARCHAR(4000), CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes) = 1) );


Learn more about SQL Server tools