JSON in SQL Server

JSON (JavaScript Object Notation) is very popular standard for exchanging data specially in REST API. Almost all modern websites, android or iOS app use this for exchanging data with server. From SQL Server 2016, Microsoft extends its support for JSON through couple of built-in functions. This way SQL Server supports NOSQL features in traditional relational databases. Let’s examine it:

  • ISJSON – examines a string whether a valid JSON or not
  • JSON_VALUE – retrieves value from a JSON string
  • JSON_QUERY – fetches object or array from a JSON string
  • JSON_MODIFY – returns the updated JSON string
  • OPENJSON – parses JSON text and returns objects and properties from the JSON input as rows and columns
  • FOR JSON Clause – exports SQL data to JSON format

ISJSON Function

DECLARE @json NVARCHAR(MAX); 
SET @json = N'{
"info":[
{
"id":"1",
"name":"Robert Aragon",
"ssn":"489-36-8350",
"credit_card":[
"4929-3813-3266-4295",
"5370-4638-8881-3020"
],
"address":{
"town":"Avon",
"area":"New York",
"zipcode":"76148"
}
},
{
"id":"2",
"name":"Thomas Conley",
"ssn":"690-05-5315",
"credit_card":[
"5299-1561-5689-1938"
],
"address":{
"town":"Jackson Street",
"area":"New York",
"zipcode":"80233"
}
},
{
"id":"3",
"name":"Susan Davis",
"ssn":"421-37-1396",
"credit_card":[
"5293-8502-0071-3058"
],
"address":{
"town":"Rock Beach",
"area":"Los angeles",
"zipcode":"900341"
}
},
{
"id":"4",
"name":"Christopher Diaz",
"ssn":"458-02-6124",
"credit_card":[
"5548-0246-6336-5664"
],
"address":{
"town":"Small town",
"area":"Wasshington",
"zipcode":"63126"
}
},
{
"id":"5",
"name":"Rick Edwards",
"ssn":"612-20-6832",
"credit_card":[
"4539-5385-7425-5825"
],
"address":{
"town":"Free Town",
"area":"Utah",
"zipcode":"97222"
}
},
{
"id":"6",
"name":"Victor Faulkner",
"ssn":"300-62-3266",
"credit_card":[
"4916-9766-5240-6147",
"4532-4220-6922-9909",
"5218-0144-2703-9266"
],
"address":{
"town":"Dakota",
"area":"North Dakota",
"zipcode":"92104"
}
}
]
}
'; 
SELECT ISJSON(@json);

Output

1 -- 1 if it is a valid JSON otherwise 0

JSON_VALUE Function

This function is used to retrieve a scalar value from a JSON string. It’s syntax is

JSON_VALUE(expression, path)

expression is the name of a variable or a column that contains JSON text and is the property to extract. For example, running the below statement on the above JSON string will yield below output :

SELECT JSON_VALUE(@json, '$.info[3].credit_card[0]')

Output

5548-0246-6336-5664

The JSON_VALUE function returns a single text value of type nvarchar(4000) . It returns null if specified path is not found in the JSON object or value is beyond nvarchar(4000).  Using ‘strict‘ keyword before the path will throw the error if specified path is not available in JSON object.

JSON_QUERY Function

The JSON_QUERY(expression [,path]) function takes the name of a variable or a column that contains JSON text and the JSON path that specifies the object or the array to extract as its arguments.

Output

It returns a JSON fragment of type nvarchar(max). Like the JSON_VALUE function returns null if specified value is not an object or an array.  Using ‘strict’ keyword will throw the error.

JSON_MODIFY Function

It updates the value of a property in a JSON string and returns the updated JSON string. It takes expression, path, and new as arguments. Using this function, we can do below operation on a JSON string:

  1. Update
  2. Insert
  3. Delete
  4. Append
1. Update

Updates the value of a given path.

SELECT JSON_MODIFY(@json,'$.info[0].name', 'Mehedi')

Output

2. Insert

New value is added in JSON string if the attribute in the provided path does not exist. Otherwise it will update the existing value as per above example. The new attribute is added at the end of the JSON.

SELECT JSON_MODIFY(@json,'$.info[0].last_name', 'Mehedi')

Output

3. Delete

Putting the NULL value in the path will just remove it.

SELECT JSON_MODIFY(@json,'$.info[0].name', NULL)

Output

4. Append

New element can be appended in array like below:

SELECT JSON_MODIFY(@json,'append $.info[0].credit_card','4539-5385-7425-5825')

Output

OPENJSON Function

This is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

DECLARE @json NVARCHAR(MAX);
SET @json =
N'{"id":"1", "name": "Robert Aragon", "ssn": "489-36-8350",
"credit_card":["4929-3813-3266-4295","5370-4638-8881-3020"], "address":
{"town": "Avon", "area": "New York", "zipcode": "76148"}}';
SELECT * FROM OpenJson(@json)
with (
    id int  '$.id',
    name varchar(50) '$.name',
    ssn varchar(50) '$.ssn',
    [credit_card] nvarchar(MAX)  AS JSON,
    [address] nvarchar(MAX)  AS JSON
)

Output

FOR JSON Clause

This clause is widely used with TSQL for exporting SQL table data to JSON format. It has two variants:

  • AUTO – Default JSON output is generated using AUTO option.
  • PATH – The structure of JSON can be modified by the column name or aliases using PATH option
IF OBJECT_ID('Test1', 'U') IS NOT NULL
    DROP TABLE Test1;
GO

-- Create the table 
CREATE TABLE Test1( 
    pk_id    int not null identity(1,1), 
    name    varchar(10) default ('Mehedi') 
) 
 GO 
 -- Populate with 3 sample data 
 INSERT INTO Test1 default values
GO 2
IF OBJECT_ID('Test2', 'U') IS NOT NULL
    DROP TABLE Test2;
GO
-- Create the table
CREATE TABLE Test2(
    pk_id    int not null identity(1,1),
    area    varchar(10) default ('Dhanmondi'),
    city    varchar(10) default ('Dhaka')
)
GO
-- Populate with 3 sample data
INSERT INTO Test2 default values
GO 1
-- Example of AUTO
SELECT A.pk_id, A.name, (SELECT pk_id, area, city
    FROM Test2 B
    WHERE A.pk_id = B.pk_id
    FOR JSON AUTO
    ) as test2
FROM Test1 A
FOR JSON AUTO
-- Example of PATH
SELECT A.pk_id, A.name, (SELECT pk_id as id, area, city
    FROM Test2 B
    WHERE A.pk_id = B.pk_id
    FOR JSON AUTO
    ) as test2
FROM Test1 A
FOR JSON PATH , ROOT ('EmployeeInfo')
GO

Output

Conclusion

This is all about the JSON in SQL Server. Happy TSQLing!

This article is taken from my blog.

About M A A Mehedi Hasan

This is M A A Mehedi Hasan working in EBS Group since 2005. Starting career as a Software Developer, I am now Chief Technology Officer (CTO) of EBS Group. In this long journey, I have got opportunity to work with various technologies like Microsoft Data Platform, Telecom Value Added Service, and Audio/Video streaming.I have hands-on experience in managing Telecom Value Added projects, Web/App/Audio/Video streaming projects. Apart from these, I also manage large database, testing and deployment, performance tuning, long term capacity planning, and streamlining operational workflow.Being a member of techforumbd (https://www.facebook.com/techforumbd), a Bangladeshi tech community, I regularly speak and organize sessions at local and virtual PASS chapters, SQL Saturdays, and Azure conferences. In free time, I love to play with Machine Learning. By the way, you can count me as a traveler too!Stay with my personal blog: www.techearth.xyz

Leave a Reply

Your email address will not be published. Required fields are marked *