SQL Server

Working With JSON In SQL Server 2016

Introduction
 
JSON (JavaScript Object Notation) is a lightweight data interchange format. It is language independent, easy to understand, and self-describing. It is used as an alternative to XML. JSON is a very popular data interchange format nowadays. Most of the modern services return the data in JSON text.
 
The Built-in JSON support in SQL server 2016 is not the same as the native JSON type. Here, JSON will be represented as a NVARCHAR type due to the following reasons.
  • Cross feature compatibility
    The data type NVARCHAR is supported all the SQL server component such as Hekaton, temporal, or column store tables etc. It works with almost all the features of SQL Server. If we think JSON works with X feature of SQL Server, the simple answer is - if NVARCHAR works with X feature, JSON will also work.

  • Migration
    Before SQL Server 2016, developers stored JSON to database as text. They needed to change database schema and migrate the data in to new feature if JSON type introduced.

  • Client-side support
    Currently, there is no standardized JSON object type in client side, such as XmlDom object. JSON is treated as Object in JavaScript.
The following in-built functions are introduced in SQL server 2016 to support JSON.
  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • OPENJSON
  • FOR JSON
ISJSON (json string)
 
It checks if the supplied NVARCHAR text input is in proper format according to JSON specification or not. This function is very useful to check input string in JSON format before it stores to the database. This function returns an INT value, if the string is properly formatted as JSON, it returns 1 else it returns 0.
 
Example

To demonstrate the example, I have taken the following JSON string.
  1. DECLARE @JSONData AS NVARCHAR(4000)  
  2. SET @JSONData = N'{  
  3.     "EmployeeInfo":{  
  4.         "FirstName":"Jignesh",  
  5.         "LastName":"Trivedi",  
  6.         "Code":"CCEEDD",  
  7.         "Addresses":[  
  8.             { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
  9.             { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
  10.         ]  
  11.     }  
  12. }'  
  1. SELECT  ISJSON(@JSONData)  
Output

SQL Server 2016
 
JSON_VALUE (json string, path)
 
It returns a scalar value from a JSON string. It parses JSON string and extracts scalar value from JSON string by specific path. There is some specific format for providing the path. For example
  • '$' - reference entire JSON object
  • '$.Property1' - reference property1 in JSON object
  • '$[2]' - reference 2nd element in JSON array
  • '$.Property1.property2[4].property3' - reference nested property in JSON object
Example

To demonstrate the example, I have used the same JSON string as used in the previous example.
  1. SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')  
SQL Server 2016
  1. SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.Addresses[0].Address')  
SQL Server 2016
 
It returns null if specified path is not found in the JSON object. If we want to throw the error if specified path is not found in JSON object, we can use 'strict' keyword prior to the path.
 
SQL Server 2016
  1. SELECT JSON_VALUE(@JSONData,'strict $.EmployeeInfo.Addresses[0].Address1')  
SQL Server 2016
 
JSON_QUERY(json string, path)
 
It is used to extract an array of data or object from the JSON string. In the following example, I have extracted "Addresses" data from JSON object and first element of "Addresses" data from JSON object.
  1. SELECT JSON_QUERY(@JSONData,'$.EmployeeInfo.Addresses')  
  2. SELECT JSON_QUERY(@JSONData,'$.EmployeeInfo.Addresses[1]')  
SQL Server 2016
 
If the JSON string contains the duplicate property; i.e., two keys with the same name and on the same level, JSON_VALUE and JSON_QUERY functions return the first value that matches the path.
  1. DECLARE @JSONData AS NVARCHAR(4000)  
  2. SET @JSONData = N'{  
  3.     "EmployeeInfo":{  
  4.         "FirstName":"Jignesh",  
  5.         "LastName":"Trivedi",  
  6.         "FirstName":"Tejas",  
  7.         "Code":"CCEEDD  
  8.     }  
  9. }'  
  10.   
  11. SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')  
SQL Server 2016
 
JSON_MODIFY (json string, path, new value)
 
We can do either insert, update, delete or append a value to the JSON string using this function. It takes three parameters, first parameter is JSON string, second parameter is path on which value needs to change and the third parameter is the value that needs to update. This function returns updated JSON string in NVARCHAR type.
 
Updating the existing value
 
To update the value of exsting JSON, we need to provide the exact path with new value. For example, using the following query we can update value of FirstName field of JSON string.
  1. SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Rakesh')  
SQL Server 2016
 
In the following example, I have updated Address field of first element of EmployeeInfo.Addresses.
  1. SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.Addresses[0].Address', 'Test Address')  
Inserting a Value
 
This function is inserting the value in JSON string if the attribute in the provided path does not exist. If the provided path is already present then it will update existing value with new value. The new attribute is always added at the end of the existing string.

In the following example, I have added MiddleName as a new attribute at EmployeeInfo root.
  1. SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName ', 'G')  
SQL Server 2016
 
Appending a Value
 
Using "append" keyword, we can append item to an existing array in JSON. In the following example, I have added a new Address object in EmployeeInfo.Addresses element.
  1. SET @JSONData = JSON_MODIFY(@JSONData,'append $.EmployeeInfo.Addresses', JSON_QUERY('{"Address":"Test 2", "City":"Bhavnagar", "State":"Gujarat"}','$'))  
SQL Server 2016
 
Multiple updates
 
Using function JSON_MODIFY, we can update only one property, if we want to update multiple properties, then we need to use multiple JSON_MODIFY calls.

In the following example, I have modified two elements: “FirstName” and “LastName” .
  1. SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Ramesh'),'$.EmployeeInfo.LastName','Oza')  
SQL Server 2016
 
Deleting existing value
 
To delete an existing value, we need to provide the full path of element and set new value to NULL.

In the following example, I have deleted the element "FirstName".
  1. SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', NULL)  
SQL Server 2016
 
Renaming Key
 
Renaming Key is not directly supported, but we can add value with new key and delete old key. In the following example, I have renamed key from “FirstName” to “ForeName”.
  1. SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.EmployeeInfo.ForeName',   
  2.     JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')),'$.EmployeeInfo.FirstName', NULL)  
SQL Server 2016
 
FOR JSON
 
Function FOR JSON is very useful when we need to export SQL table data as JSON format. It is very similar to FOR XML function. Here, column names or aliases are used as key names for JSON object. There are two options with FOR JSON
  • AUTO: It will create nested JSON sub array based on the table hierarchy used in the query.
  • PATH: It enable us to define the structure of JSON that is required using the column name or aliases. If we put dot (.) separated names in the column aliases, JSON properties follows the same naming convention.
The FOR JSON AUTO is suitable for most scenarios but FOR JSON PATH is very useful in specific scenarios where we need to control how JSON data is generated or nested. The FOR JSON PATH give sus full control to specify the output format for JSON data.
 
Syntax
  1. SELECT COL1, COL 2 FROM   
  2. TABLE   
  3. FOR JSON AUTO| PATH  
Example

To demonstrate the example, I have created two tables: EmployeeInfo and Addresses. The ratio between tables is as follows. I have also inserted some data.

SQL Server 2016 
  1. GO  
  2. CREATE TABLE [dbo].[Addresses](  
  3.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  4.     [EmployeeId] [int] NULL,  
  5.     [Address] [varchar](250) NULL,  
  6.     [City] [varchar](50) NULL,  
  7.     [State] [varchar](50) NULL,  
  8.  CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED   
  9. (  
  10.     [Id] ASC  
  11. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  12. ) ON [PRIMARY]  
  13.   
  14. GO  
  15. CREATE TABLE [dbo].[EmployeeInfo](  
  16.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  17.     [Code] [varchar](50) NULL,  
  18.     [FirstName] [varchar](50) NULL,  
  19.     [LastName] [varchar](50) NULL,  
  20.  CONSTRAINT [PK_EmployeeInfo] PRIMARY KEY CLUSTERED   
  21. (  
  22.     [Id] ASC  
  23. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  24. ) ON [PRIMARY]  
  25.   
  26. GO  
  27. SET IDENTITY_INSERT [dbo].[Addresses] ON   
  28.   
  29. GO  
  30. INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (1, 1, N'Test 0', N'Gandhinagar', N'Gujarat')  
  31. GO  
  32. INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (2, 1, N'Test 1', N'Bhavnagar', N'Gujarat')  
  33. GO  
  34. INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (3, 2, N'Test 2', N'Bhavnagar', N'Gujarat')  
  35. GO  
  36. INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (4, 2, N'Test 3', N'Gandhinagar', N'Gujarat')  
  37. GO  
  38. SET IDENTITY_INSERT [dbo].[Addresses] OFF  
  39. GO  
  40. SET IDENTITY_INSERT [dbo].[EmployeeInfo] ON   
  41.   
  42. GO  
  43. INSERT [dbo].[EmployeeInfo] ([Id], [Code], [FirstName], [LastName]) VALUES (1, N'ABCD', N'Jignesh', N'Trivedi')  
  44. GO  
  45. INSERT [dbo].[EmployeeInfo] ([Id], [Code], [FirstName], [LastName]) VALUES (2, N'XYZ', N'Rakesh', N'Trivedi')  
  46. GO  
  47. SET IDENTITY_INSERT [dbo].[EmployeeInfo] OFF  
  48. GO  
  49. ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_EmployeeInfo] FOREIGN KEY([EmployeeId])  
  50. REFERENCES [dbo].[EmployeeInfo] ([Id])  
  51. GO  
  52. ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_EmployeeInfo]  
  53. GO  
Exanple - FOR JSON AUTO
  1. SELECT * FROM [dbo].[EmployeeInfo] e  
  2. INNER JOIN [dbo].[Addresses] Addresses ON e.Id = Addresses.EmployeeId  
  3. WHERE e.Id = 1  
  4. FOR JSON AUTO  
Output JSON
  1. [  
  2.   {  
  3.     "Id": 1,  
  4.     "Code": "ABCD",  
  5.     "FirstName": "Jignesh",  
  6.     "LastName": "Trivedi",  
  7.     "Addresses": [  
  8.       {  
  9.         "Id": 1,  
  10.         "EmployeeId": 1,  
  11.         "Address": "Test 0",  
  12.         "City": "Gandhinagar",  
  13.         "State": "Gujarat"  
  14.       },  
  15.       {  
  16.         "Id": 2,  
  17.         "EmployeeId": 1,  
  18.         "Address": "Test 1",  
  19.         "City": "Bhavnagar",  
  20.         "State": "Gujarat"  
  21.       }  
  22.     ]  
  23.   }  
  24. ]  
Example - FOR JSON PATH
  1. SELECT Id, Code, FirstName, LastName,  
  2.     (SELECT Id, Address, City, State  
  3.     FROM [dbo].[Addresses] a  
  4.     WHERE a.EmployeeId = e.Id  
  5.     FOR JSON AUTO  
  6.     ) as Addresses  
  7. FROM [dbo].[EmployeeInfo] e  
  8. WHERE e.Id =1  
  9. FOR JSON PATH, ROOT ('EmployeeInfo')  
Output
  1. {  
  2.   "EmployeeInfo": [  
  3.     {  
  4.       "Id": 1,  
  5.       "Code": "ABCD",  
  6.       "FirstName": "Jignesh",  
  7.       "LastName": "Trivedi",  
  8.       "Addresses": [  
  9.         {  
  10.           "Id": 1,  
  11.           "Address": "Test 0",  
  12.           "City": "Gandhinagar",  
  13.           "State": "Gujarat"  
  14.         },  
  15.         {  
  16.           "Id": 2,  
  17.           "Address": "Test 1",  
  18.           "City": "Bhavnagar",  
  19.           "State": "Gujarat"  
  20.         }  
  21.       ]  
  22.     }  
  23.   ]  
  24. }  
OPENJSON
 
It is a table value function that will generate relational table with its contents from the JSON string. It will iterate through JSON object elements, arrays and generate a row for each elements. We can generate table either without a pre-defined schema or with a well-defined schema.
 
OPENJSON With-out a Pre-defined Schema

In this functionality the value will be returned as key-value pairs including their type. In the following example, it shows JSON data as key-value pair with its type.
  1. DECLARE @JSONData AS NVARCHAR(4000)  
  2. SET @JSONData = N'{  
  3.         "FirstName":"Jignesh",  
  4.         "LastName":"Trivedi",  
  5.         "Code":"CCEEDD",  
  6.         "Addresses":[  
  7.             { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
  8.             { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
  9.         ]  
  10.     }'  
  11.   
  12.   
  13. SELECT * FROM OPENJSON(@JSONData)  
SQL Server 2016
 
OPENJSON with a Pre-defined Schema
 
OPENJSON function can also generate a result set with pre-defined schema. If we generate results with pre-defined schema, it generates a table based on provided schema instead of key-value pair.
f
  1. DECLARE @JSONData AS NVARCHAR(4000)  
  2. SET @JSONData = N'{  
  3.         "FirstName":"Jignesh",  
  4.         "LastName":"Trivedi",  
  5.         "Code":"CCEEDD"       
  6.     }'  
  7.   
  8. SELECT * FROM OPENJSON(@JSONData)  
  9. WITH (FirstName VARCHAR(50),  
  10. LastName VARCHAR(50),  
  11. Code VARCHAR(50))  
SQL Server 2016
 
We can also access child JSON object as well using the OPENJSON function. This can be done by CROSS APPLYing the JSON child element with parent element.
 
In the following example, the EmployeeInfo object and the Addresses object are fetched and applied to Cross join on. We need to use the "AS JSON" option in column definition to specify which references the property that contains child JSON node is. The column specified with "AS JSON" option, type must be NVARCHAR (MAX). Without this option, this function returns NULL value instead of child JSON object and also it returns a run time error in "strict" mode.
  1. DECLARE @JSONData AS NVARCHAR(4000)  
  2. SET @JSONData = N'{  
  3.         "FirstName":"Jignesh",  
  4.         "LastName":"Trivedi",  
  5.         "Code":"CCEEDD",  
  6.         "Addresses":[  
  7.             { "Address":"Test 0", "City":"Bhavnagar", "State":"Gujarat"},  
  8.             { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
  9.         ]  
  10.     }'  
  11.   
  12. SELECT   
  13. FirstName, LastName, Address, City, State   
  14. FROM OPENJSON(@JSONData)  
  15. WITH (FirstName VARCHAR(50),  
  16. LastName VARCHAR(50),  
  17. Code VARCHAR(50),  
  18. Addresses NVARCHAR(max) as json  
  19. ) as B  
  20. cross apply openjson (B.Addresses)  
  21. with  
  22. (  
  23.     Address VARCHAR(50),  
  24.     City VARCHAR(50),  
  25.     State VARCHAR(50)  
  26. ) A  
SQL Server 2016
 
Summary
 
All modern web applications support JSON and it is one of the well-known data interchange formats. Now, SQL Server also supports the JSON format. There is no specific data type for JSON SQL Server like XML. We need to use NVARCHAR when we interact with JSON.
 
There are many built-in functions available with SQL Server 2016, such as ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON, and FOR JSON. Using these functions, we can play around with the JSON object.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs