Sql Server JSON result separated in multiple rows [closed]

Question

Closed. This question needs debugging details. It is not currently accepting answers.

Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.

Closed 3 hours ago.

Improve this question

I would like to know if this is normal behavior of SQL Server (on Azure). My SQL query returns JSON using FOR JSON PATH and nested queries. My problem is that it seems that depending on the length of the resulting JSON, the result is separated into multiple rows.

This is a problem since the number of rows might change depending on the data. I would also have to concat all the rows together manually to have a valid JSON.

A workaround is to put the resulting JSON into a variable and select it after hand. This results in a single row and column (so I know it is not a max length issue).

Is this normal behavior or is there a problem with my query?

Jovan MSFT · Accepted Answer · 2017-01-28 21:37:16Z

3

Sql Server splits result of FOR JSON query into ~2KB chunk, so you should either concatenate fragments like on the MSDN page or you can stream results into some output stream.

Here you can find ASP.NET Core REST API implementation where results of the SQL query with FOR JSON is streamed into response body: https://www.codeproject.com/Articles/1106622/Building-REST-services-with-ASP-NET-Core-Web-API-a

answered Jan 28, 2017 at 21:37

Jovan MSFT

14.8k4 gold badges44 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joeldesante Over a year ago

Hi, can you please provide a link to the documentation which supports this? I can't seem to find anything in the Microsoft documentation that mentions 2 kb chunking.

Chaki_Black · Accepted Answer · 2025-12-14 11:02:59Z

-1

To prevent a FOR JSON AUTO response from being split into multiple rows, you should assign the result to a variable of type VARCHAR(MAX). This forces SQL Server to buffer the entire JSON string as a single value before returning it to the client application.

DECLARE @jsonResult NVARCHAR(MAX);

SELECT @jsonResult = (
    SELECT
        Column1,
        Column2
    FROM
        YourTable
    FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER
);

SELECT @jsonResult AS JSON_Output;

or cross-link is here: https://dba.stackexchange.com/a/279614/23923

edited Dec 14 at 11:02

answered Dec 13 at 8:24

Chaki_Black

9304 gold badges13 silver badges32 bronze badges

1 Comment

Gert Arnold Dec 13 at 10:51

Policy: Generative AI (e.g., ChatGPT) is banned.

Collectives™ on Stack Overflow

Sql Server JSON result separated in multiple rows [closed]

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related