What is JSON :¶
JSON (Java script notation) is light weight data interchange format.It is language independent, Easy to understand and self_describing. It is used as alternative to XML.
There is no specific datatype for JSON SQL server like XML.We need to use NVARCHAR when we interact with JSON.
The bulit_in JSON support in SQL server 2016 is not the same as native JSON type.
Different types of JSON :¶
The following in_built functions are introduced in SQL server 2016 to support JSON.
1)ISJSON 2)JSON_VALUE 3)JSON_QUERY 4)JSON_MODIFY 5)FORJSON 6)OPENJSON
1) ISJSON():¶
It checks whether we have valid JSON or not.
This function validates whether the string parameter supplied to it is a valid JSON then it will return value as 1, otherwise it returns the value 0. Incase input is a NULL then it returns output as NULL.
Syntax: select ISJSON( expression )
Where expression can be a table column or a string (i.e. VARCHAR/NVARCHAR) variable or a string constant and this expression is evaluated to check whether it is a valid JSON.
Example 1: Input is a valid JSON Retutns 1
1 2 3 4 5 6 7 8 9 | |
Example 2: Input is an Invalid JSON ,returns 0.
1 2 3 | |
Example 3: If input is NULL ,Returns NULL.
1 | |
2)JSON_VALUE():¶
It returns 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.
It has a JSON expression defined as Key (Name) and its value ("xyz")
It specifies $.Name as argument path. Yhis path should reference the key in the JSON expression.
Example :
1 2 3 4 5 6 7 8 9 | |
3) JSON_Query():¶
JSON_QUERY is one of the new JSON fonction introduced in SQL server 2016 to Query the JSON data. JSON_Query basically returns the JSON fragment(i.e. JSON object or an array) from the input JSON string from the specified JSON path.
Syntax : select JSON_QUERY(json_string,json_path)
Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 | |
4)JSON_MODIFY() :¶
JSON_QUERY is one of the new JSON fonction introduced in SQL server 2016. This function can be used to update the value of the property in a JSON string and returns the updated JSON string.
Syntax: select JSON_MODIFY(json_string,json_path,new_value)
Updating the Existing value :¶
To update the value of existing JSON, we need to provide the exact path with new value. For example ,using the following query we can update the value firstname of JSON string.
Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
Inserting a value :¶
This function is for 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 the existing value with new value. The new attribute always added at the end of the existing string.
Inthe following example , i have added MiddleName as a new attribute at EmployeeInfo root.
Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | |
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'.
Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
Deleting Existing value :¶
To delete existing value , we need to provide full path of element and set new value to NULL.
In the following example, I have deleted the element 'FirstName' . Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
Renamming 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'.
Example :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | |
5)FORJSON :¶
Function FOR JSON is very useful when we need to export SQL tabledata 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 :¶
I t will create nested JSON sub array based on the table hierarchy used in the query.
Example FOR JSON AUTO :
select * from Admin_Trn_Modules a inner join Admin_Trn_Permissions b on a.ModuleId=b.ModuleId where a.ModuleId=2 for json auto
PATH :¶
It enable us to define the structure of the JSON that is required using the column name or aliases. If we put dot(.) seperated names in the column aliases JSON properties follows the same naming convention.
The FOR JSON AUTO is very suitable for most scenarios but FOR JSON PATH is very useful in specific scenarios where we need to controlhow JSON data is generated or nested. The FOR JSON PATH gives us full controlls to specify the output format for JSON data.
Example FOR JSON PATH :
select ModuleId,ModuleName,ModuleCode, (Select PermissionId,PermissionName,PermissionCode from Admin_Trn_Permissions a where a.ModuleId=b.ModuleId FOR JSON AUTO) as Permission from Admin_Trn_Modules b where b.ModuleId=1 FOR JSON PATH,ROOT ('Info')
6) OPENJSON :¶
It is table valued function that will generate relational table with its contenets from JSON string. It will iterate through JSON object elements, arrays and generate a row for each elements. We can generate table either with out a pre-defined schema or with a well-defined schema.
###### OPENJSON with out apre-defined schema : In this functionality the value will be returned as a key-value pairs including their type. In the following example , It shows JSON data as a key-value pair wiyh its type.
Example : ``` declare @JSONData nvarchar(max) 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 * from OPENJSON(@JSONData) ``` ###### OPENJSON with 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.
Example :
```
declare @JSONData nvarchar(max)
set @JSONData=N'{
"FirstName":"Jignesh",
"LastName":"Trivedi",
"Code":"CCEEDD"
}'
select * from OPENJSON(@JSONData) WITH(FirstName varchar(50), LastName varchar(50), Code varchar(50)) ```