Home » SQL Server FOR JSON Clause

SQL Server FOR JSON Clause

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 understand the FOR JSON with the help of an example, first we create a studentInfo table and insert some sample records into a 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 caluse can generate JSON, in case if you do not have any table and passing hard- coded values.

SELECT 'Microsoft SQL Server 2016' as DatabaseName, 
'Microsoft Corporation' as OwnedBy
FOR JSON PATH

FOR JSON AUTO, can not generate JSON without table, it requires at least one table to generate JSON.

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.”

Generate JSON from table result set

Using FOR JSON PATH to generate JSON from table

SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo
FOR JSON PATH

As you can see, It generate table output in Json format, In select statement it is not mandatory to pass all table column name, we can pass any specific column as well. Using FOR JSON AUTO to generate JSON from table

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 ouptut.

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 Caluse with DOT “.” Symobol

FOR JSON PATH CLAUSE Using dot “.” symbol with FOR JSON PATH caluse in the column aliases, we 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 caluse 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 , we need to add INCLUDE_NULL_VALUES  property in the FOR JSON clause otherwise in case of null value the name-value pair will be removed from the JSON output To demonstrate this, Lets add one more sample record in table having null values.

INSERT INTO dbo.StudentInfo (StudName, StudCode, StudAddress)
VALUES ('Ankur Singh', 'D78FJHI', null)
GO

As you can see, now we have one null value for studAddress column.

SELECT * FROM dbo.StudentInfo

Lets see what happens, if we do not use ROOT option with FOR JSON AUTO CLAUSE. Remember this time we have 3 rows in our table and third row having null value for 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 we want to include null values in JSON result we can use INCLUDE_NULL_VALUES  property. FOR JSON AUTO

SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo
FOR JSON AUTO, INCLUDE_NULL_VALUES 

 

 

FOR JSON PATH

SELECT StudId, StudName, StudCode, StudAddress FROM dbo.StudentInfo
FOR JSON PATH, INCLUDE_NULL_VALUES

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.