FOR JSON Outputs in Multiple Rows

Question

I am trying to retrieve table information using FOR JSON, as the size of the data is more I am getting the result in multiple rows.

As per this article SSMS concatenates the results into a single row when the output setting is Results to Grid.
https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15

However, I am still receiving the results in different rows with 2033 chars in each row.

Please advise how I can retrieve JSON response in one row with no carriage returns.

Attached my sql query and response.

Answer 1

Nah, I am not sure that SSMS still has it right. If I do

SELECT TOP 200 * FROM Customers FOR JSON AUTO

and then click on the document, SSMS barfs about malformed XML. If I try TOP 20, I can open, but it is one single line, and no nice formattering.

No, to look at JSON, you are better off using Azure Data Studio which has true JSON support. If you download the most recent version of SSMS, 18.7, Azure Data Studio is included in the download.

Answer 2

Hi @KP ,

Thank you so much for posting here.

Please refer below options and check whether any of them is helpful to you.

Option 1: Download the latest SSMS.

Option 2: Use print instead of select like below:

DECLARE @result NVARCHAR(max);  
  
SET @result = (SELECT * FROM table  
           FOR JSON AUTO, ROOT('Data'))  
  
PRINT @result;  

Option 3: Download and use Azure Data Studio which is a multi-platform re-write of SSMS (similar to how Visual Studio was re-written as VS Code).

Option 4: Try using FOR XML instead. For example:

SELECT STUFF((  
    SELECT ', '+name FROM sys.columns FOR XML PATH(''), TYPE  
).value('.','nvarchar(max)'),1,2,'')  

Option 5: Use cast result to varchar format. For example:

SELECT CAST((SELECT [Columns] FROM [Tables] FOR JSON PATH('')) AS VARCHAR(MAX)) AS JSONDATA  

Option 6: Insert into a temp table.For example:

declare @json table (j nvarchar(max));  
insert into @json select * from(select* from Table where Criteria1 for json auto)a(j)  
select * from @json  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 3

Hi @KP ,

I tried with below code to reproduce your issue.

use AdventureWorks2017  
go  
SELECT  a.*  
FROM person.person  a  
inner join person.BusinessEntity b  
on  a.BusinessEntityID=b.BusinessEntityID  
FOR JSON path,root('customer')  

After some tries, I could have no truncated json data which length was more than 2 million in my output using below method.

• Change from Results to Grid to Results to Text or Results to file(save as a .txt)(Query-Results To-Results to Text/Results to file).
  
  I will update you if I have any other finding. Thanks.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 4

Hi @KP ,

I tried with below two queries in SSMS 18.5 and 18.7 (set results to Grid), I could get the maximum 65535 characters while only 42679 in SSMS 17.4.

--method 1  
  
DECLARE @result NVARCHAR(max);  
      
 SET @result = (SELECT  a.*  
FROM person.person  a  
inner join person.BusinessEntity b  
on  a.BusinessEntityID=b.BusinessEntityID  
FOR JSON path,root('data'))  
      
 SELECT @result;  
  
--method 2  
 declare @json table (j nvarchar(max));  
  
 insert into @json   
 select * from  
 (SELECT  a.*  
FROM person.person  a  
inner join person.BusinessEntity b  
on  a.BusinessEntityID=b.BusinessEntityID  
FOR JSON path,root('data'))a(j)  
  
 select * from @json  

I recommend you to upgrade the SSMS to latest version and have another try.

Since 65535 characters is the maximum value set in SSMS, if you still need more than 80k characters , you may consider to use other tool.

You could also refer below link for more methods which may be helpful to you.
SQL Server json truncated (even when using NVARCHAR(max) )

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 5

For what it's worth, I ran this in grid mode in SSMS:

 declare @json table (j nvarchar(max));
 insert into @json SELECT *FROM (SELECT TOP 200 * FROM Customers FOR JSON AUTO )a(j)
 select len(j), * from @json

I copied the contents in the second grid cell to the query window and went to the end of the line. It did indeed seem like the end of a JSON document. I also noticed that it said "Col 54546" in the status bar, and that agreed with the output from the len function.

It appears that Melissa is not updated on SSMS. Since some release, you can set the maximum length for a grid cell to 2 million, 2000000.

However, how much I hate to say it, the best option when you work with JSON is Azure Data Studio. SSMS is crippled when it comes to JSON.