Forum Discussion
FOR JSON AUTO Query returning broken headers
Hello all,
I wanted to confirm if there's anything I'm doing wrong or if this is actually a bug.
I am using SQL Server Express 2022 version 16.0.1000.6 with all patches installed. I am sharing a copy of the database as it is right now if anyone wants to test it:
https://1drv.ms/f/s!ApzieZYERmuWhZlQofO7ejX0xT8DyA?e=KG3mtu
When I run the following query:
SELECT [Datetime]
,[Temperature]
,[Snow]
,[SolarRadiation]
,[UVIndex]
,[Visulazation]
,[Description]
,[WindSpeed]
,[Sunrise]
,[Sunset]
,[Precipitation]
,[City]
,[Country]
,[Pressure]
,[Wind_Direction]
,[FeelsLike]
FROM [WeatherStation].[dbo].[Weather]
ORDER BY [Datetime] DESC
FOR JSON AUTO
I get the JSON result, then I right-click on SSMS where the result is shown and select "Save Result As ...", select a path, and give my file a name with JSON extension.
When I open the file, I get lines 223 without JSON formatting, line 658 with a space or line break on "Description" and on line 767 I get a space or line break for the "Light Rain" record.
I am opening the file with Visual Sutdio code.
Are my records broken and need adjustment? My query needs adjustments? Any suggestions on how to proceed?
6 Replies
As described in the following article, the returned JSON object is shown in SSMS as a single string, but in reality can be several rows.
In the result, the rows are separated by a newline.
https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver16&tabs=json-path#output-of-the-for-json-clauseI tested your described export path on your database, and when I opened the "result.json" file, I got a file with 33 rows of compacted JSON.
But since they were 33 rows, they were not _correct_ JSON, as the newlines separating these rows can happen at any point in the data.
Which means you need to concatenate your data before you format it out to readable JSON.
- Open your extracted file in VS Code
- Open the Find/Replace function
- Activate "RegEx" mode
- Find \n
- Replace with nothing
- Click Replace All
- Format
Newly opened Export with the find replace dialog open (ctrl + h)
After Replace
After Format (alt+shift+f)
select "Save Result As ...", select a path, and give my file a name with JSON extension.brunosimoes , just changing the file extention don't change the file format and default for export is CSV, so yes, you get additional semicolons, which are not valid for JSON.
No bug, that's how the export works.
Note, SSMS is mainly for managing SQL Server, not intended for such tasks.
olafhelper Thank you.
Any effective way to convert the query result into a valid JSON in SQL Serevr?
brunosimoes SQL Server already returns valid JSON, your way to export it breaks the result.
