FOR JSON PATH results in SSMS truncated to 2033 characters

Question

I'm concatenating strings together using "for JSON path('')".

I have set the Tools->Options->SQL Server->Results to Grid options to max.

I have set the Tools->Options->SQL Server->Results to Text options to max.

Executing the query in Grid mode and copying the one row/one column results, I see the return value is limited to 2033 characters.

How can I ensure the returned value isn't truncated?

Answer 1

The behavior is documented here:

A large result set splits the long JSON string across multiple rows.

By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.

Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

Therefore, using FOR JSON to concatenate strings (when the result is longer than 2033 bytes) is not the best idea.

Try using FOR XML instead. For example:

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

Answer 2

Insert into a nvarchar(max) variable or a table will do it.

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

or

DECLARE @JSON nvarchar(max)
SET @JSON = (SELECT * FROM Table FOR JSON AUTO)
SELECT @JSON

Answer 3

I've noticed that it suffices to just put the JSON (or XML) into a scalar subquery:

-- Useless
select row_number() over(order by (select 1)) as rn
from sys.all_objects
for json auto

-- Useful
select (
  select row_number() over(order by (select 1)) as rn
  from sys.all_objects
  for json auto
)

... at least in newer versions of SQL Server such as e.g. 2019. dbfiddle demo here