SqlDataReader and SQL Server 2016 FOR JSON splits json in chunks of 2k bytes

Question

Recently I played around with the new for json auto feature of the Azure SQL database.

When I select a lot of records for example with this query:

Select
    Wiki.WikiId
    , Wiki.WikiText
    , Wiki.Title
    , Wiki.CreatedOn
    , Tags.TagId
    , Tags.TagText
    , Tags.CreatedOn
From
    Wiki
Left Join
    (WikiTag
Inner Join 
    Tag as Tags on WikiTag.TagId = Tags.TagId) on Wiki.WikiId = WikiTag.WikiId
For Json Auto

and then do a select with the C# SqlDataReader:

var connectionString = ""; // connection string
var sql = "";  // query from above
var chunks = new List();

using (var connection = new SqlConnection(connectionString)) 
using (var command = connection.CreateCommand()) {
    command.CommandText = sql;
    connection.Open();

    var reader = command.ExecuteReader();

    while (reader.Read()) {
            chunks.Add(reader.GetString(0)); // Reads in chunks of ~2K Bytes
    }
}

var json = string.Concat(chunks);

I get a lot of chunks of data.

Why do we have this limitation? Why don't we get everything in one big chunk?

When I read a nvarchar(max) column, I will get everything in one chunk.

Thanks for an explanation

user_352349 · Answer

As a workaround in the SQL code (i.e. if you don't want to change your querying code to put the chunks together), I found that wrapping the query in a CTE and then selecting form that gives me the results I expected:

--Note that I query from information_schema to just get a lot of data to replicate the problem.

--doing this query results in multiple rows (chunks) returned
SELECT * FROM information_schema.columns FOR JSON PATH, include_null_values

--doing this query results in a single row returned
;WITH SomeCTE(JsonDataColumn) AS
(
    SELECT * FROM information_schema.columns FOR JSON PATH, INCLUDE_NULL_VALUES
) 
SELECT JsonDataColumn FROM SomeCTE

The first query reproduces the problem for me (returns multiple rows, each a chunk of the total data), the second query gives one row with all the data. SSMS wasn't good for reproducing the issue, you have to try it out with other client code.

SqlDataReader and SQL Server 2016 FOR JSON splits json in chunks of 2k bytes

2 Answers