SQL Server FOR JSON Clause
SQL Server FOR JSON Clause is used to format query result to JSON format or export data from SQL Server as JSON format. It was introduced in SQL Server 2016 version. Using FOR JSON clause, you can specify the structure of the JSON output explicitly with the help of PATH and AUTO as given below.
- ย FOR JSON PATH gives the full control over the format of the JSON output also can create wrapper objects and nest complex properties.
- FOR JSON AUTO is used to format the JSON output automatically based on the structure of the SELECT statement.
Lets look at an example using FOR JSON clause in SQL Server.
First we create a sample table named as studentInfo and Insert some sample records in table.
CREATE TABLE dbo.StudentInfo (StudId INT IDENTITY(1,1) PRIMARY KEY, StudName NVARCHAR(100), StudCode NVARCHAR(50), StudAddress NVARCHAR(50) ) GO INSERT INTO dbo.StudentInfo (StudName, StudCode, StudAddress) VALUES ('Daniel Jr', 'D78GHKJ', 'Street -3, Block 5c'), ('Rozer Hanery', 'D78JKHI', 'Condominum 3A, 3rd floor') GO
SELECT * FROM dbo.StudentInfo
Lets understand the use of FOR JSON PATHย and FOR JSON AUTO clause
FOR JSON PATH Clause
Using FOR JSON PATH clause you can generate JSON, in case if you do not have any table and passing hard-coded values as shown below.
SELECT 'Microsoft SQL Server 2016' as DatabaseName, 'Microsoft Corporation' as OwnedBy FOR JSON PATH
FOR JSON AUTO
Using FOR JSON AUTO clause, you can not generate JSON without table.
It requires at least one table to generate JSON when using FOR JSON AUTO clause.
Lets try to generate JSON without table, just gives hard-coded values.
SELECT 'Microsoft SQL Server 2016' as DatabaseName, 'Microsoft Corporation' as OwnedBy FOR JSON AUTO
As you can see, It gives an error syaingย “FOR JSON AUTO requires at least one table for generating JSON objects. Use FOR JSON PATH or add a FROM clause with a table name.”
Lets generate JSON from table using FOR JSON PATH clause
Following statement uses FOR JSON Path clause and generates JSON from table StudentInfo.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON PATH
As you can see, It converts table output in Json format.
In select statement it is not mandatory to pass all table column’s name.
You can provide specific columns as well.
FOR JSON AUTO Clause
Using FOR JSON AUTO clause to generate JSON from table StudentInfo.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON AUTO
Using JSON with ROOT Option
Using the ROOT option in the FOR JSON clause can be generated a wrapper object around the generated JSON output.
For JSON PATH with ROOT Option
Following statement uses the ROOT option with For JSON PATH clause and creates a studentInformation JSON wrapper object around the JSON output.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON PATH, ROOT('StudentInformation')
For JSON AUTO with ROOT Option
Following statement uses the ROOT option with For JSON AUTO clause and creates a studentInformation JSON wrapper object around the JSON ouptut.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON AUTO, ROOT('StudentInformation')
FOR JSON Clause with DOT “.” Symbol
FOR JSON PATH CLAUSE using dot “.” symbol with FOR JSON PATH clause in the column aliases, you can name the each object in the resulting JSON array.
SELECT StudId [StudentInfo.StudID], StudName [StudentInfo.StudName], StudCode [StudentInfo.StudCode], StudAddress [StudentInfo.StudAddress] FROM dbo.StudentInfo FOR JSON PATH, ROOT('StudentInformation')
FOR JSON AUTO CLAUSE Using dot “.” symbol with FOR JSON AUTO clause in the column aliases, does not affect in the generated JSON.
SELECT StudId [StudentInfo.StudID], StudName [StudentInfo.StudName], StudCode [StudentInfo.StudCode], StudAddress [StudentInfo.StudAddress] FROM dbo.StudentInfo FOR JSON AUTO, ROOT('StudentInformation')
Including null values in JSON Result
To display null values in JSON result set, you need to add INCLUDE_NULL_VALUES property in the FOR JSON clause otherwise in case of null values the name-value pair will be removed from the JSON output To demonstrate this, Lets add one more sample record in table containing a null values.
INSERT INTO dbo.StudentInfo (StudName, StudCode, StudAddress) VALUES ('Ankur Singh', 'D78FJHI', null) GO
As you can see, now you have one null value in studAddress column.
SELECT * FROM dbo.StudentInfo
Lets see what happens, if you do not use ROOT option with FOR JSON AUTO CLAUSE.
Remember this time you have three rows in table and third row contains null value in studAddress column.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON AUTO
As you can see, JSON removed the name-value pair for null value in result set. So if you want to include null values in JSON result, you can use INCLUDE_NULL_VALUESย property, FOR JSON AUTO
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON AUTO, INCLUDE_NULL_VALUESย
As you can see, now null values is not removed from JSON.
You can also useย INCLUDE_NULL_VALUESย with FOR JSON PATH to including null value as shown below.
SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo FOR JSON PATH, INCLUDE_NULL_VALUES
Also Read..
10,374 total views, 1 views today