Working with JSON in SQL Server

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 trendy data interchange format nowadays. Most modern services return the data in JSON text. SQL Server JSON is one of the needs for data developers to return JSON in SQL Server. In this article, let's learn how to implement JSON objects in SQL Server.

The Built-in JSON support in the SQL server is different from the native JSON type. JSON will be represented as an NVARCHAR type for the following reasons.

• Cross feature compatibility
  The data type NVARCHAR has supported all the SQL server components such as Hekaton, temporal, column store tables, etc. It works with almost all the features of SQL Server. If we think JSON works with the X feature of SQL Server, the simple answer is that if NVARCHAR works with the X feature, JSON will also work. 
• Migration
  Before SQL Server, developers stored JSON in the database as text. They needed to change the database schema and migrate the data into a new feature if JSON type was introduced. 
• Client-side support
  Currently, there is no standardized JSON object type on the client side, such as an XmlDom object. JSON is treated as Object in JavaScript.

The following in-built functions are introduced in SQL Server to support JSON.

• ISJSON
• JSON_VALUE
• JSON_QUERY
• JSON_MODIFY
• OPENJSON
• FOR JSON

ISJSON (json string)

This function is very useful for checking the input string in JSON format before it stores in the database. It checks whether the supplied NVARCHAR text input is in the proper format according to JSON specification. This function returns an INT value; if the string is correctly formatted as JSON, it returns 1. Else it returns 0.

Example

To demonstrate the example, I have taken the following JSON string.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
    "EmployeeInfo":{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD",  
        "Addresses":[  
            { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
            { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
        ]  
    }  
}' 

SELECT  ISJSON(@JSONData) 

Output

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 a 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

I used the exact JSON string as the previous example to demonstrate the example.

SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')  

SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.Addresses[0].Address')  

It returns null if the specified path is not found in the JSON object. If we want to throw the error if the specified path is not found in the JSON object, we can use the 'strict' keyword before the path.

SELECT JSON_VALUE(@JSONData,'strict $.EmployeeInfo.Addresses[0].Address1')  

JSON_QUERY(json string, path)

It extracts an array of data or objects from the JSON string. In the following example, I have extracted "Addresses" data from the JSON object and the first element of "Addresses" data from the JSON object.

SELECT JSON_QUERY(@JSONData,'$.EmployeeInfo.Addresses')  
SELECT JSON_QUERY(@JSONData,'$.EmployeeInfo.Addresses[1]')  

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.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
    "EmployeeInfo":{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "FirstName":"Tejas",  
        "Code":"CCEEDD  
    }  
}'  
  
SELECT JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')  

JSON_MODIFY (json string, path, new value)

This function returns an updated JSON string in NVARCHAR type. It takes three parameters; the first parameter is the JSON string, the second parameter is a path on which the value needs to change, and the third parameter is the value that needs to update. Using this function, we can insert, update, delete, or append a value to the JSON string.

Updating the existing value

To update the value of the existing JSON, we need to provide the exact path with the new value. For example, we can update the value of the FirstName field of the JSON string using the following query.

SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Rakesh')  

In the following example, I have updated the Address field of the first element of EmployeeInfo.Addresses.

SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.Addresses[0].Address', 'Test Address')  

Inserting a Value

This function inserts the value in the JSON string if the attribute in the provided path does not exist. If the provided path is already present, it will update the existing value with the new one. 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 the EmployeeInfo root.

SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.MiddleName ', 'G')  

Appending a Value

Using the "append" keyword, we can append an item to an existing array in JSON. In the following example, I have added a new Address object in EmployeeInfo.Addresses element.

SET @JSONData = JSON_MODIFY(@JSONData,'append $.EmployeeInfo.Addresses', JSON_QUERY('{"Address":"Test 2", "City":"Bhavnagar", "State":"Gujarat"}','$'))  

Multiple updates

Using the function JSON_MODIFY, we can update only one property; if we want to update multiple properties, we need to use multiple JSON_MODIFY calls.

In the following example, I have modified two elements: "FirstName" and "LastName."

SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', 'Ramesh'),'$.EmployeeInfo.LastName','Oza')  

Deleting existing value

To delete an existing value, we need to provide the full path of the element and set a new value to NULL.

In the following example, I have deleted the element "FirstName."

SET @JSONData = JSON_MODIFY(@JSONData,'$.EmployeeInfo.FirstName', NULL)  

Renaming Key

Renaming the Key is not directly supported, but we can add value with the new key and delete the old key. In the following example, I have renamed the key from "FirstName" to "ForeName."

SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.EmployeeInfo.ForeName',   
    JSON_VALUE(@JSONData,'$.EmployeeInfo.FirstName')),'$.EmployeeInfo.FirstName', NULL) 

FOR JSON

Function FOR JSON is very useful when exporting SQL table data in JSON format. It is very similar to FOR XML function. Here, column names or aliases are key names for JSON objects. There are two options FOR JSON.

• AUTO - It will create nested JSON sub-array based on the table hierarchy used in the query.
• PATH - It enables us to define the required JSON structure using the column name or aliases. If we put dot (.) separated names in the column aliases, JSON properties follow 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 must control how JSON data is generated or nested. The FOR JSON PATH gives us full control to specify the output format for JSON data.

Syntax

SELECT COL1, COL 2 FROM   
TABLE   
FOR JSON AUTO| PATH 

Example

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

GO  
CREATE TABLE [dbo].[Addresses](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [EmployeeId] [int] NULL,  
    [Address] [varchar](250) NULL,  
    [City] [varchar](50) NULL,  
    [State] [varchar](50) NULL,  
 CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
CREATE TABLE [dbo].[EmployeeInfo](  
    [Id] [int] IDENTITY(1,1) NOT NULL,  
    [Code] [varchar](50) NULL,  
    [FirstName] [varchar](50) NULL,  
    [LastName] [varchar](50) NULL,  
 CONSTRAINT [PK_EmployeeInfo] PRIMARY KEY CLUSTERED   
(  
    [Id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY]  
  
GO  
SET IDENTITY_INSERT [dbo].[Addresses] ON   
  
GO  
INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (1, 1, N'Test 0', N'Gandhinagar', N'Gujarat')  
GO  
INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (2, 1, N'Test 1', N'Bhavnagar', N'Gujarat')  
GO  
INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (3, 2, N'Test 2', N'Bhavnagar', N'Gujarat')  
GO  
INSERT [dbo].[Addresses] ([Id], [EmployeeId], [Address], [City], [State]) VALUES (4, 2, N'Test 3', N'Gandhinagar', N'Gujarat')  
GO  
SET IDENTITY_INSERT [dbo].[Addresses] OFF  
GO  
SET IDENTITY_INSERT [dbo].[EmployeeInfo] ON   
  
GO  
INSERT [dbo].[EmployeeInfo] ([Id], [Code], [FirstName], [LastName]) VALUES (1, N'ABCD', N'Jignesh', N'Trivedi')  
GO  
INSERT [dbo].[EmployeeInfo] ([Id], [Code], [FirstName], [LastName]) VALUES (2, N'XYZ', N'Rakesh', N'Trivedi')  
GO  
SET IDENTITY_INSERT [dbo].[EmployeeInfo] OFF  
GO  
ALTER TABLE [dbo].[Addresses]  WITH CHECK ADD  CONSTRAINT [FK_Addresses_EmployeeInfo] FOREIGN KEY([EmployeeId])  
REFERENCES [dbo].[EmployeeInfo] ([Id])  
GO  
ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_EmployeeInfo]  
GO

Example - FOR JSON AUTO

SELECT * FROM [dbo].[EmployeeInfo] e  
INNER JOIN [dbo].[Addresses] Addresses ON e.Id = Addresses.EmployeeId  
WHERE e.Id = 1  
FOR JSON AUTO 

Output - JSON

[  
  {  
    "Id": 1,  
    "Code": "ABCD",  
    "FirstName": "Jignesh",  
    "LastName": "Trivedi",  
    "Addresses": [  
      {  
        "Id": 1,  
        "EmployeeId": 1,  
        "Address": "Test 0",  
        "City": "Gandhinagar",  
        "State": "Gujarat"  
      },  
      {  
        "Id": 2,  
        "EmployeeId": 1,  
        "Address": "Test 1",  
        "City": "Bhavnagar",  
        "State": "Gujarat"  
      }  
    ]  
  }  
]

Example - FOR JSON PATH

SELECT Id, Code, FirstName, LastName,  
    (SELECT Id, Address, City, State  
    FROM [dbo].[Addresses] a  
    WHERE a.EmployeeId = e.Id  
    FOR JSON AUTO  
    ) as Addresses  
FROM [dbo].[EmployeeInfo] e  
WHERE e.Id =1  
FOR JSON PATH, ROOT ('EmployeeInfo')  

Output

{  
  "EmployeeInfo": [  
    {  
      "Id": 1,  
      "Code": "ABCD",  
      "FirstName": "Jignesh",  
      "LastName": "Trivedi",  
      "Addresses": [  
        {  
          "Id": 1,  
          "Address": "Test 0",  
          "City": "Gandhinagar",  
          "State": "Gujarat"  
        },  
        {  
          "Id": 2,  
          "Address": "Test 1",  
          "City": "Bhavnagar",  
          "State": "Gujarat"  
        }  
      ]  
    }  
  ]  
}

OPENJSON

A table value function will generate a relational table with its contents from the JSON string. It will iterate through JSON object elements and arrays and generate a row for each element. We can generate a table without a pre-defined schema or a well-defined schema.

OPENJSON Without a Pre-defined Schema

This functionality will return the value as key-value pairs, including their type. The following example shows JSON data as key-value pair with its type.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD",  
        "Addresses":[  
            { "Address":"Test 0", "City":"Gandhinagar", "State":"Gujarat"},  
            { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
        ]  
    }'  
  
  
SELECT * FROM OPENJSON(@JSONData)  

OPENJSON with a Pre-defined Schema

OPENJSON function can also generate a result set with a pre-defined schema. If we generate results with a pre-defined schema, it generates a table based on provided schema instead of key-value pair.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD"       
    }'  
  
SELECT * FROM OPENJSON(@JSONData)  
WITH (FirstName VARCHAR(50),  
LastName VARCHAR(50),  
Code VARCHAR(50))  

We can also access child JSON objects as well using the OPENJSON function. This can be done by CROSS APPLYing the JSON child element with the parent element.

In the following example, the EmployeeInfo and Addresses objects are fetched and applied to Cross join on. We need to use the "AS JSON" option in the column definition to specify which references the property that contains the child JSON node. In the column specified with the "AS JSON" option, the type must be NVARCHAR (MAX). Without this option, this function returns a NULL value instead of a child JSON object and returns a run time error in "strict" mode.

DECLARE @JSONData AS NVARCHAR(4000)  
SET @JSONData = N'{  
        "FirstName":"Jignesh",  
        "LastName":"Trivedi",  
        "Code":"CCEEDD",  
        "Addresses":[  
            { "Address":"Test 0", "City":"Bhavnagar", "State":"Gujarat"},  
            { "Address":"Test 1", "City":"Gandhinagar", "State":"Gujarat"}  
        ]  
    }'  
  
SELECT   
FirstName, LastName, Address, City, State   
FROM OPENJSON(@JSONData)  
WITH (FirstName VARCHAR(50),  
LastName VARCHAR(50),  
Code VARCHAR(50),  
Addresses NVARCHAR(max) as json  
) as B  
cross apply openjson (B.Addresses)  
with  
(  
    Address VARCHAR(50),  
    City VARCHAR(50),  
    State VARCHAR(50)  
)

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.

Many built-in functions are available with SQL Server, such as ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON, and FOR JSON. Using these functions, we can play around with the JSON object.