Learn more about SQL Server tools

mssqltips logo

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


SQL Server 2016 Advanced JSON Techniques - Part 1

By:   |   Read Comments   |   Related Tips: More > SQL Server 2016

Attend a SQL Server Conference for FREE >> click to learn more


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 Update:

signup button

next tip button

About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for about two decades, first earning the Microsoft MVP award in 1997.

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    Notify for updates 

SQL tips:

*Enter Code refresh code     

Learn more about SQL Server tools