Working With JSON In SQL Server 2016

Jignesh Trivedi
7y
9k
0
6

Article

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.

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

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

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

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.

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

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.

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)

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.

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

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

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.

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

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.

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

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” .

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 element and set 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 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”.

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

SELECT COL1, COL 2 FROM
TABLE
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.

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

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

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.

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 pre-defined schema. If we generate results with 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 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.

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)
) A

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

Practical SQL for Data Analysts: Unlock the Power of Your Data

Read by 0 people

Download Now!

Learn

View all