Introduction
Nowadays, JSON is the most popular language tool to communicate between different technologies, and I have seen many developers communicating with SQL serve with JSON. It is very essential to know how we handle JSON as an input and out in SQL serve.
Note: This article is written with respect to MS SQL server and their might be some technical detail which can mismatch with other RDBMS.
Table of Content
- In-build function to handle JSON
- Converting JSON array to table
- Converting Table data to JSON
1. In-build function to handle JSON
In SQL server we have some in-build functions or we can call it system functions using which we can handle JSON out JSON. In-build functions for JSON handling are:
- ISJSON
- JSON_VALUE
- JSON_QUERY
- JSON_MODIFY
ISJSON
Before start working on JSON, we should have to check wether the JSON on which we supposed to work is a valid JSON or not. ISJOSN is a In-build function which will take the JSON an argument and return value 1 if the JSON is valid and return value 0
if the JSON is not valid.
Syntax
ISJSON( JSON_Expression ) JSON_VALUE
If we want a scaler value from JSON_Expression then we can use JSON_VALUE in-build function.
JSON_VALUE we return scaler value out of the passed JSON_Expression if found. If JSON_Value function didn't found the value in JSON_Expression than it will throw an error.
Syntax
JSON_VALUE( JSON_Expression , path ) JSON_VALUE function will take two argument as following
- JSON_Expression : A valid JSON
- Path: Actual path of the scaler value in the JSON_Expression.
Note: path should start from "$."
Example: Let us take simple example of a JSON_Expression as mentioned below which consist of car data.
{
"Type": "SUV",
"Manufacture": "Tata",
"Model": [
{
"Name": "Nexon",
"Engine": "4 cylinder"
},
{
"Name": "PUNCH",
"Engine": "3 cylinder"
}
]
}Consider we want to car name of model present at second index of Model array, or we want "PUNCH" car name. So the SQL expression for this would be:
Declare @JSON_Expression varchar(max)='{
"Type": "SUV",
"Manufacture": "Tata",
"Model": [
{
"Name": "Nexon",
"Engine": "4 cylinder"
},
{
"Name": "PUNCH",
"Engine": "3 cylinder"
}
]
}'
Select JSON_VALUE( @JSON_Expression,'$.Model[1].Name' ) as Car_Name JSON_QUERY
Sometimes there is a situation where we only requires a portion of a JSON, and the required portion is not a scaler value but instead, it is an array or object. JSON_QUERY is in-build function that will return an expression of type varchar.
Syntax:
JSON_QUERY( JSON_Expression , path )JSON_QUERY function will take two arguments as follows
- JSON_Expression: A valid JSON
- Path: Actual path of the required value in the JSON_Expression.
Note: path should start from "$."
Example: Let us take a simple example of a JSON_Expression as mentioned below which consists of car data.
{
"Type": "Hatchback",
"Manufacture": "Maruti Suzuki",
"Model": [
{
"Name": "Alto",
"Engine": "4 cylinder"
},
{
"Name": "Baleno",
"Engine": "4 cylinder"
}
]
}Consider we want a list of Models from JSON array, So the SQL expression for this would be:
Declare @JSON_Expression varchar(max)='{
"Type": "SUV",
"Manufacture": "Tata",
"Model": [
{
"Name": "Nexon",
"Engine": "4 cylinder"
},
{
"Name": "PUNCH",
"Engine": "3 cylinder"
}
]
}'
Select JSON_QUERY( @JSON_Expression,'$.Model' ) as Modal_Array
[{"Name": "Nexon","Engine": "4 cylinder"},{"Name": "PUNCH","Engine": "3 cylinder"}]JSON_MODIFY
In the above function we saw how we can get extract the data from the JSON_Expression, but which function could help us in situations where we have the modify the actual JSON_Expression.
JSON_MODIFY is an in-build function, which allows us to modify the passed JSON_Expression and return a new modified JSON_Expression.
We can only modify JSON_Expression in below mentioned ways:
- we can update JSON object property value(that value could be a string, a new object, or an array).
- we can add a new property in the JSON object.
- We can add a new value to the JSON object.
Syntax
JSON_MODIFY( JSON_Expression ,[append] path , newValue) - JSON_Expression : A valid JSON
- Path: Actual path of the propert in the JSON_Expression which need to be changed. And if you want to add newvalue to an array use append as prefix to path.
- NewValue: New value which will be replcaed with the old value and if you want to add new value to an array newValue will be appended to the array.
{ "Type": "Sadan",
"Manufacture": "Toyota",
"Model": [
{
"Name": "Civic",
"Engine": "4 cylinder"
},
{
"Name": "City",
"Engine": "4 cylinder"
}
]
}case 1: Considering we want to change the manufacture data from Toyota and change to Honda, So the SQL expression for this would be
Declare @JSON_Expression varchar(max)='{
"Type": "Sadan", "Manufacture": "Toyota",
"Model": [
{
"Name": "Civic",
"Engine": "4 cylinder"
},
{
"Name": "City",
"Engine": "4 cylinder"
}
]
}'
Select JSON_MODIFY( @JSON_Expression,'$.Manufacture','Honada' ) as New_JSON
Output:
{"Type": "Sadan","Manufacture": "Honada","Model": [{"Name": "Civic","Engine": "4 cylinder"},{"Name": "City","Engine": "4 cylinder"} ]}case 2: Considering we want to add a new car model to the Model array, So the SQL expression for this would be
declare @JSON_Expression varchar(max)='{
"Type": "Sadan",
"Manufacture": "Toyota",
"Model": [
"Civic",
"City"
]
}'
Select JSON_MODIFY( @JSON_Expression, 'append $.Model','Amaze' ) as New_JSONOutput:
{
"Type": "Sadan",
"Manufacture": "Toyota",
"Model": [
"Civic",
"City",
"Amaze"
]
}2. Converting JSON array to Table
In SQL, we can convert an array JSON into a table using OPENJSON in-build function. Converting array JSON to the table will help you in performing SQL operations on your array JSON.
Syntax:
Select * from OPENJSON(JSON_Expression)Note: Keep in mind that JSON you are passing to the OPENJSON function should contain valid array only.
3. Converting Table Result Set to JOSN
If we want to convert the table data into JSON then SQL serve provide us two way to do that as mentioned below.
- For JOSN AUTO: JOSN AUTO will warp your table result set into JSON. The resulting JSON will be converted automatically based on the data present table result set. As the AUTO implies, most of the conversion is handled by the SQL itself so we have less control over the resulting JOSN
Syntax:
Select * from Table For Json Auto- For JSON PATH: JOSN PATH will warp your table result set into the JSON same as the JSON Auto does but in JOSN Path you have more control and you can mold the outcome JSON as per your requirement
Syntax:
Select * from Table For Json Path Options Available for "FOR JSON"
- ROOT OPTION: Root option allows you to set the main root of the JSON output
Syntax:
Select * from TableName For JSON Path , ROOT('JSON_ROOT')
- INCLUDE_NULL_VALUES: Used to allow SQL to include a null value in resulted JSON output
- WITHOUT_ARRAY_WRAPPER: Used to remove "[]" in the resulted JSON output from the result set
Conclusion
At the end of this article, I hope you understand how we are working with JSON in our SQL server. We can easily retrieve data out of a JSON expression and make effective use of our queries, we can also convert our JSON array into the table so that we can easily perform our query operation on them, and last but not least we can convert our table data into JSON expression.
I hope you learned something valuable after reading this article. Leave down a comment if you have any questions or any suggestion. Thank you for reading this article.
0 Comments