How to Export Table Data to JSON Format in SQL Server

In this article we will learn How to Export Table Data to JSON Format in SQL Server. SQL Server provides multiple ways to export data from a table into JSON format. Here’s a step by step procedure.

Create a sample data table

CREATE TABLE Employee(
	Id int IDENTITY(1,1) NOT NULL,
	FirstName nvarchar(100) NOT NULL,
	LastName nvarchar(100) NOT NULL,
	designation nvarchar(50) NOT NULL,
	Title nvarchar(100) NOT NULL,
	Email nvarchar(25) NOT NULL,
	Role  nvarchar(15) NOT NULL,
	Password nvarchar(15) NOT NULL
	);

2. Insert sample data into the table Employee

INSERT INTO [dbo].[Employee]
           ([FirstName]
           ,[LastName]
           ,[designation]
           ,[Title]
           ,[Email]
           ,[Role]
           ,[Password])
     VALUES
           ('Mick','Fleming','Software Developer','Mr','Mick@example.com','Admin','123Admin'),
		   ('Stuart','Fleming','Software Developer','Mr','Stuart@example.com','Admin','123Admin');

Table of Contents

FOR JSON Clause

For JSON Clause in a SELECT Statement to convert the data from a table into JSON format

SELECT * FROM EMPLOYEE FOR JSON AUTO

Result

[{"Id":5,"FirstName":"Mick","LastName":"Fleming","designation":"Software Developer","Title":"Mr","Email":"Mick@example.com","Role":"Admin","Password":"123Admin"},{"Id":6,"FirstName":"Stuart","LastName":"Fleming","designation":"Software Developer","Title":"Mr","Email":"Stuart@example.com","Role":"Admin","Password":"123Admin"}]

FOR JSON PATH

To control over the format of the JSON output, use FOR JSON PATH.

SELECT * FROM EMPLOYEE FOR JSON PATH, Root('EmployeeList')

{"EmployeeList":[{"Id":5,"FirstName":"Mick","LastName":"Fleming","designation":"Software Developer","Title":"Mr","Email":"Mick@example.com","Role":"Admin","Password":"123Admin"},{"Id":6,"FirstName":"Stuart","LastName":"Fleming","designation":"Software Developer","Title":"Mr","Email":"Stuart@example.com","Role":"Admin","Password":"123Admin"}]}