Exporting data from SQL Server as CSV is well understood and works (most of the time). But most often when I create a CSV file, I only use it as an intermediate format that I then convert to JSON.
In SQL Server 2016 and newer we get the FOR JSON clause that we can use with SELECT to get the data as JSON directly from SQL Server:
|
1 2 3 4 5 6 7 8 9 |
SELECT TOP (3) [BusinessEntityID] AS 'Id' ,[Title] ,[FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks2019].[Person].[Person] WHERE BusinessEntityID > 4 FOR JSON AUTO |
This query creates this JSON snipped:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[{ "Id": 5, "Title": "Ms.", "FirstName": "Gail", "MiddleName": "A", "LastName": "Erickson" }, { "Id": 6, "Title": "Mr.", "FirstName": "Jossef", "MiddleName": "H", "LastName": "Goldberg" }, { "Id": 7, "FirstName": "Dylan", "MiddleName": "A", "LastName": "Miller" } ] |
For a small set of data, I just append FOR JSON AUTO to my query and I get JSON without the detour to CSV.
Caveat: Size limit in older SSMS
SQL Server Management Studio (SSMS) was not built to let you export big junks of data as JSON. In older versions you may run into the limit of 2033 characters and then your JSON may stop in the middle of an entry.
I tried to reproduce this problem with SSMS 18.8 (released in December 2020) but could not get that error. With the current version I could export the whole AdventureWorks Product table (resulting in 9535 lines of JSON) without a problem.
The SalesOrderDetail table with more than 120’000 rows took its time but at the end I got file with 1.4 million lines of valid JSON.
Therefore, if you run into a problem with cut-off JSON upgrade your SQL Server Management Studio.
Conclusion
If you need to convert your data to JSON you can leverage the “FOR JSON” clause of SQL Server 2016 and newer. It works astonishingly well with the current version of SQL Server Management Studio and makes the intermediate step of exporting to CSV obsolete.
Thank you. a lot of useful information