JSON is a very popular standard for exchanging data specially in REST API which stands for JavaScript Object Notation. 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. Hence, supports NOSQL features in traditional relational databases. Let's delve 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, if we run the below statement on the above JSON string, the output would be:
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]) 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 arraystrict' 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:
- Update
- Insert
- Delete
- 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!