How to export a table to JSON file in SQL Server?

Recently for one of my projects, I had to export data from a table to a JSON file. To export a table to a JSON file, I have used the FOR JSON clause along with the select statement to format the retrieved data as JSON and then I ran the select statement as BCP command through XP_CMSSHELL in SSMS.

For this illustration I have used the table Application.Cities in Microsoft’s sample database WideWorldImporters. To start with, here is the select statement to fetch the required data from the Application.Cities table:

1. Simple select statement to get the required data.

SELECT 
	CityID, 
	CityName,
	LatestRecordedPopulation
FROM Application.Cities
GO

2. Convert the result set to JSON format

Now we have to format the above result set to JSON. To do this, add the FOR JSON PATH class. If you want to include the null values in the JSON result, then add INCLUDE_NULL_VALUES after a comma. On executing this select statement, you will get the result in JSON format in a single line.

/* Get table data in JSON format */
SELECT 
	CityID, 
	CityName,
	LatestRecordedPopulation
FROM Application.Cities
	FOR JSON PATH, 
		INCLUDE_NULL_VALUES
GO

If you notice, the JSON result looks like a link. On pressing, it opens the result as a xml file on a separate window.

3. Export the result in JSON format to a JSON file

To export the JSON formatted result to a json file, use the BCP along with XP_CMDSHELL to run the script we have created. Here is the sample script:

/* Export JSON data to a file */
DECLARE @sql varchar(1000)
SET @sql = 'bcp "SELECT CityID, CityName, ' + 
	'LatestRecordedPopulation FROM Application.Cities ' + 
	'FOR JSON PATH, INCLUDE_NULL_VALUES" ' +
	'queryout  "c:\Beaulin-Temp\cities.json" ' + 
	'-c -S MACWIN2 -d WideWorldImporters -T'
EXEC sys.XP_CMDSHELL @sql
GO

NOTE: While executing the xp_cmdshell for the first time on your server you may get an error as xp_cmdshell is disabled by default. Enable the command shell using sp_configure to proceed further.

Here is the JSON file created by BCP:

Exporting line-delimited JSON format

The file created from the above example will have the data in a single line. In case, if you want to have every JSON object in a separate line, then modify the select statement with a subquery to return the output in line-delimited format. You also need to include WITHOUT_ARRAY_WRAPPER to remove the array wrapper created in every line.

/* Get table data in line delimited JSON format */
SELECT (
	SELECT 
		CityID, 
		CityName,
		LatestRecordedPopulation
	FOR JSON PATH, 
		INCLUDE_NULL_VALUES, 
		WITHOUT_ARRAY_WRAPPER
	)
FROM Application.Cities
GO

Here is the result:

Then, run the select statement using BCP for exporting line delimited JSON to a file.

/* Export data formatted with line delimitation to a JSON file */
DECLARE @sql varchar(1000)
SET @sql = 'bcp "SELECT (SELECT CityID, CityName, ' +
	'LatestRecordedPopulation ' +
	'FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER) ' +
	'FROM Application.Cities" ' +
	'queryout  "c:\Beaulin-Temp\cities.json" ' + 
	'-c -S MACWIN2 -d WideWorldImporters -T'
EXEC sys.XP_CMDSHELL @sql
GO

Here is the JSON file created by BCP. Every row of data is in a separate line.

Reference

• More about formatting query results as JSON at Microsoft Docs.