Exploring Microsoft SQL Server’s Support for JSON Data

Posted on by Editor

Exploring Microsoft SQL Server's Support for JSON Data

One powerful development tool for connecting front end development to SQL data is using the built-in JSON support in SQL Server 2016.

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for machines to parse and operate on data. Some of the benefits of the JSON is it’s independent, easy to understand, and self-describing.

Microsoft SQL Server 2016 has built-in support for JSON but there are some key considerations.  SQL Server 2016 does not support the native JSON type it is instead represented as an NVARCHAR type.  This is done for the following reasons:

  • Cross Feature Compatibility – NVARCHAR is supported by a range of SQL Server components such as Hekaton, column store tables, etc. With the NVARCHAR support, you can be assured that if a feature of SQL Server supports NVARCHAR then it will also support JSON.
  • Migration – In versions of SQL Server before 2016, JSON data was typically stored as text. By keeping the NVARCHAR format it doesn’t require migration of older data to a new data type.
  • Client-Side Support – There is not a JSON object standard type for the client-side / front end. JSON in Javascript is handled as an Object.

database

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

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

Here is a sample SQL Server script to create a table with some sample data. We are going to use this table in some of our examples below.

CREATE TABLE [dbo].[StudentInfo](
  [StudentID] [int] IDENTITY(1,1) NOT NULL,
  [Name_Json] [nvarchar](100) NULL,
  [Address_Json] [nvarchar](300) NULL,
  [Subjects_Json] [nvarchar](50) NULL,
  [ParentsInfo_Json] [nvarchar](500) NULL
) ON [PRIMARY]

GO
INSERT INTO [dbo].[StudentInfo]
([Name_Json],[Address_Json],[Subjects_Json],[ParentsInfo_Json])
VALUES
('{"FirstName":"John","MiddleName":"Dan","LastName":"Doe"}',
'{"AddressesInfo":[{"Address":"Address line 1","City":"Austin","State":"TX","Zip":"75077"},
{"Address":"Address line wow","City":"Protin","State":"TX","Zip":"75094"}]}',
'["ASP.NET","Flutter","PHP"]',
'{"ParentsInfo":[{"Name":"Dan Doe","Relation":"Father","Phone":"9020202"},
{"Name":"Riya Doe","Relation":"Mother","Phone":"9039393"}]}')

GO

INSERT INTO [dbo].[StudentInfo]
([Name_Json],[Address_Json],[Subjects_Json],[ParentsInfo_Json])
VALUES
('{"FirstName":"John","MiddleName":"Ron","LastName":"Woe"}',
'{"AddressesInfo":[{"Address":"Address line 3","City":"Boston","State":"TX","Zip":75078},
{"Address":"Address line 9","City":"Vitamin","State":"TX","Zip":75094}]}',
'["Networking","Flutter","WordPress"]',
'{"ParentsInfo":[{"Name":"Ron Woe","Relation":"Father","Phone":"9038383"},
{"Name":"Mia Woe","Relation":"Mother","Phone":"9083833"}]}')

GO

INSERT INTO [dbo].[StudentInfo]
([Name_Json],[Address_Json],[Subjects_Json],[ParentsInfo_Json])
VALUES
('{"FirstName":"Mike","MiddleName":"Do","LastName":"Warner"}',
'{"AddressesInfo":[{"Address":"Address line 2","City":"Denver","State":"TX","Zip":75075},
{"Address":"Address line wow","City":"Atlanta","State":"TX","Zip":75094}]}',
'["WordPress","Flutter","AngularJS"]',
'{"ParentsInfo":[{"Name":"Do Warner","Relation":"Father","Phone":"9023939"},
{"Name":"Rima Warner","Relation":"Mother","Phone":"9039393"}]}')

Function: ISJSON

This function checks if the string is a valid JSON object which can be done before saving it to the database. The function returns an integer value. If the string is valid JSON then it returns 1 else it returns 0.

Syntax: ISJSON ( expression )

 Example: In this example we are checking if Parent info data sent is a valid JSON data.

Declare @JsonOBJ nvarchar(200)

SET @JsonOBJ = '{"ParentsInfo":[{"Name":"Dan Doe","Relation":"Father","Phone":"9020202"},
{"Name":"Riya Doe","Relation":"Mother","Phone":"9039393"}]}'

SELECT ISJSON(@jsonOBJ)

JSON data

 

Function: JSON_VALUE

This function returns a scalar value from JSON object. It parses JSON strings and extracts scalar values. Below are some examples utilizing the path argument.

Syntax: JSON_VALUE ( expression , path ) The path can have following values
1) ‘$’ – will give entire JSON object
2) ‘$.propertyname’ – will give the value of propertyname in JSON object
3) ‘$.[3]’ – will fetch 4th element in the array
4) ‘$.prop1.Prop2[0].prop3’ – will fetch nested property value

Example: In this example, we are fetching specific values from the JSON object. The function JSON_VALUE can be used as part of the select statement as well as in where clause. Here we are finding all students from the city of Austin.

select JSON_VALUE([Name_Json],'$.FirstName') as name, 
JSON_VALUE(ParentsInfo_Json,'$.ParentsInfo[0].Name')as ParentName, 
JSON_VALUE(ParentsInfo_Json,'$.ParentsInfo[0].Phone') as Phone
FROM [StudentInfo] 
WHERE JSON_VALUE([Address_Json],'$.AddressesInfo[0].City') = 'Austin'

json value

 

Function: JSON_QUERY

This function extracts a JSON array or JSON object from the JSON String.

Syntax: JSON_QUERY (expression , path )

Example: here in this query we are fetching JSON address data from Address_Json field

select JSON_QUERY(Address_Json,'$.AddressesInfo[0]') as Address1, 
JSON_QUERY(Address_Json,'$.AddressesInfo[1]') as Address2
FROM StudentInfo

JSON_QUERY

So in short if you want to fetch any specific value from a JSON object, you can use the JSON_VALUE function and if you need a JSON array data or a JSON object you have to use function JSON_QUERY

Function: JSON_MODIFY

We can use this function to insert, update, delete and append values into JSON object. The function takes 3 parameters. The first parameter is a JSON string, the second parameter is the path at which the value will be added/updated. The third parameter is the value that will be added/updated.

Syntax: JSON_MODIFY ( expression , path , newValue )

1) Update value – To update a value in JSON object, we need to provide exact path and new value.

Example:

declare @JSON_Obj as varchar(200)

set @JSON_Obj = '{"AddressesInfo":
[{"Address":"Address line 1","City":"Austin","State":"TX","Zip":"75077"},
 {"Address":"Address line wow","City":"Protin","State":"TX","Zip":"75094"}]}'

set @JSON_Obj = JSON_MODIFY(@JSON_Obj,'$.AddressesInfo[0].State','AL')

select @JSON_Obj

JSON_Obj

2) Insert value

This function can be used to insert a new key value pair into the JSON object, If the path in the function is already present then the current value is updated otherwise a new attribute is created and a new value is assigned.

Example: In this example, we are adding a new attribute Country to the JSON data:

declare @JSON_Obj as varchar(200)

set @JSON_Obj = '{"AddressesInfo":
[{"Address":"Address line 1","City":"Austin","State":"TX","Zip":"75077"},
{"Address":"Address line wow","City":"Protin","State":"TX","Zip":"75094"}]}'

set @JSON_Obj = JSON_MODIFY(@JSON_Obj,'$.AddressesInfo[0].Country','USA')

select @JSON_Obj

JSON_Obj insert value

3) Delete Value

To delete an existing value, we have to provide the exact path with a value of NULL. This will delete the element from the JSON object.

Example: In this example, we are removing the attribute County from the JSON Data

declare @JSON_Obj as varchar(200)

set @JSON_Obj = '{ "state": "NY", "County": "Manhattan", "city": "NY" }'

set @JSON_Obj = JSON_MODIFY(@JSON_Obj,'$.County',NULL)

select @JSON_Obj

JSON_Obj delete value

Function: OPENJSON

This function can be used to convert the JSON object into a set of rows and columns. After transforming the JSON object we can run a SQL query on it or it can be used to save data into tables.

In this example we are converting JSON object to SQL table:

select @JSON_Obj

Declare @Address_OBJ as nvarchar(300)

SET @Address_OBJ = '{"FirstName":"John", "LastName":"Doe",  
"AddressesInfo":{"Address":"Address line 1","City":"Austin","State":"TX","Zip":"75077"}}'

SELECT  *
FROM OPENJSON(@Address_OBJ)
WITH
  ( FirstName VARCHAR(100) '$.FirstName',
    LastName VARCHAR(100) '$.LastName',
    Address VARCHAR(100) '$.AddressesInfo.Address',
    State VARCHAR(50)	'$.AddressesInfo.State',  
    Zip VARCHAR(10)  '$.AddressesInfo.Zip',
    City VARCHAR(50) '$.AddressesInfo.City' 
  )

OPENJSON

 

Function: FOR JSON

This function can be used to convert SQL query results into JSON format object. While creating a JSON object from a SQL query, the column names or aliases are used as keys in the JSON object.

  • Use FOR JSON AUTO to format the JSON object automatically based on a SQL SELECT statement use the
  • Use FOR JSON PATH for more control over JSON output use if you want the result in s specific structure

The following example will explain how to use FOR JSON function

Example AUTO – If we just simply want SQL result into JSON format object we can user AUTO option

Select StudentID,Name_Json,Subjects_Json 
FROM StudentInfo FOR JSON AUTO

FOR JSON

This function can be used to convert SQL query results into JSON format object. While creating a JSON object from a SQL query, the column names or aliases are used as keys in the JSON object.

  • Use FOR JSON AUTO to format the JSON object automatically based on a SQL SELECT statement use the
  • Use FOR JSON PATH for more control over JSON output use if you want the result in s specific structure

The following example will explain how to use FOR JSON function

Example AUTO – If we just simply want SQL result into JSON format object we can user AUTO option

Select StudentID,Name_Json,Subjects_Json 
FROM StudentInfo FOR JSON AUTO

FOR JSON AUTO

Example PATH. If you want more control over key names and structure of JSON object, you can use path option to export data.

Select StudentID as 'ID',
Name_Json as 'StudentInfo',
Subjects_Json as 'SubjectInfo'

FROM StudentInfo FOR JSON PATH, ROOT ('StudentInfo')

FOR JSON PATH

 

Conclusion: With SQL server 2016, we can store and process both relational data and JSON data using standard Transact-SQL language. We can save fixed columns as well as JSON objects with flexible key-value pairs in the same table and perform the operation on them.