Issue where clause FOR JSON AUTO has generated the incomplete answer

Question

Getting JSON from SQL Server is great, but I ran into a problem. Example. I have a LithologySamples table with a very basic structure:

    [Id] [uniqueidentifier],
    [Depth1] [real],
    [Depth2] [real],
    [RockId] [nvarchar](8),

In the database there are more or less 600 records of this table. I want to generate a JSON to transport data to another database, so I use FOR JSON AUTO. Which has worked perfectly with other tables with less records. But in this case I see that the response is generated incomplete. It has me baffled. I noticed when examining the output:

[{
        "Id": "77769039-B2B7-E511-8279-DC85DEFBF2B6",
        "Depth1": 4.2000000e+001,
        "Depth2": 5.8000000e+001,
        "RockId": "MIC SST"
    }, {
        "Id": "78769039-B2B7-E511-8279-DC85DEFBF2B6",
        "Depth1": 5.8000000e+001,
        "Depth2": 6.3000000e+001,
        "RockId": "CGL"
    }, {
        "Id": "79769039-B2B7-E511-8279-DC85DEFBF2B6",
        "Depth1": 6.3000000e+001,
        "Depth2": 8.3000000e+001,
        "RockId": "MIC SST"
    }, {
// ... OK, continue fine, but it breaks off towards the end:
    }, {
        "Id": "85769039-B2B7-E511-8279-DC85DEFBF2B6",
        "Depth1": 2.0500000e+002,
        "Depth2": 2.1500000e+002,
        "RockId": "MIC SST"
    }, {
        "Id": "86769039-
// inexplicably it cuts here !?

I've searched and I can't find any options for the answer to come out complete.

The SQL query is as follows:

SELECT*FROM LithologySamples FOR JSON AUTO;

AUTO or PATH are the same result

Does anyone know what I should do so that the statement generates the JSON of the entire table?

From [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-ver15#output-of-the-for-json-clause): _A large result set splits the long JSON string across multiple rows., ... 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_ — Zhorov, May 21 '22 at 16:35

Aaron Bertrand · Answer 1 · 2022-05-22T17:21:38.397

5

But in this case I see that the response is generated incomplete.

If you are checking this in SSMS, it truncates text in various ways depending on the output method you're using (PRINT, SELECT, results to text/grid). The string is complete, it's just the output that has been mangled.

One way to validate that the string is in fact complete is to:

SELECT * INTO #foo FROM 
  (SELECT * FROM LithologySamples FOR JSON AUTO) x(y);

Then checking LEN(y), DATALENGTH(y), RIGHT(y , 50) (see example db<>fiddle), or selecting from that table using CONVERT(xml (see this article for more info).

In your case it seems the problem is coming from how C# is consuming the output. If the consumer is treating the JSON as multiple rows, then assigning a variable there will ultimately assign one arbitrary row of <= 2033 characters, not the whole value. I talked about this briefly back in 2015. Let's say you are using reader[0] or similar to test:

CREATE TABLE dbo.Samples
(
    [Id] [uniqueidentifier] NOT NULL DEFAULT NEWID(),
    [Depth1] [real] NOT NULL DEFAULT 5,
    [Depth2] [real] NOT NULL DEFAULT 5,
    [RockId] [nvarchar](8)
);

INSERT dbo.Samples(RockId) SELECT TOP (100) LEFT(name, 8) FROM sys.all_columns;

-- pretend this is your C# reader:
SELECT * FROM dbo.Samples FOR JSON AUTO;

-- reader[0] here would be something like this:
-- [{"Id":"054EC9A2-760B-4EBA-BF06-...,"RockId":"ser

-- which is the first 2,033 characters
SELECT LEN('[{"Id":"054EC9A2-760B-4EBA-BF06-..."RockId":"ser')

-- instead, since you want C# to assign a scalar, 
-- assign output to a scalar first:
DECLARE @json nvarchar(max) = (SELECT * FROM dbo.Samples FOR JSON AUTO);
SELECT json = @json;

-- now reader[0] will be the whole thing

Example db<>fiddle

The 2033 comes from the same place it comes from for XML (since SQL Server's JSON implementation is just a pretty wrapper under existing underlying XML functionality), as Charlie points out Martin explained here:

SELECT FOR XML AUTO and return datatypes

edited May 22 '22 at 17:21

answered May 21 '22 at 14:48

Aaron Bertrand

272,866
37
466
490

Hello. The issue is not really with the result in the output window of SSMM. I get the result from a C# application, obtaining the scalar of the query. I try you suggest, it shows different values in LEN and DATALENGTH, however the result is still cut off (I removed `RIGHT(y, 50)` as a sample to the article and left only `y`) – Alexandra Danith Ansley May 21 '22 at 15:42
1

@AlexandraDanithAnsley The `RIGHT(y, 50)` was used to show that the JSON is _closed correctly_ and that the content in those last 50 characters don't match the last 50 characters ot the truncated result you're seeing somewhere else. The fault is still in _the client_... how are you using C# to validate / display the data? What data type are you using, what method are you using to render, where is _that_ data being displayed and checked? The problem isn't in _the query_ or in _SQL Server_ so we don't have the right information in the question to help fix it. – Aaron Bertrand May 21 '22 at 16:17
I found the solution: `DECLARE @JSON nvarchar(max);SET @JSON = (SELECT * FROM LithologySamples FOR JSON AUTO);SELECT @JSON;` The Scalar now returns complete json data – Alexandra Danith Ansley May 21 '22 at 17:07
@AlexandraDanithAnsley Was it previously cutting off at 4000 or 8000 characters? It means something somewhere along the way assumed the output was 4000 or 8000 characters and applied an implicit conversion. And yes if the consumer is treating the JSON as _multiple rows_, then assigning a variable _there_ will ultimately assign _one arbitrary row_ of <= 2033 characters, not the whole value. I talked about this briefly [back in 2015](https://www.sentryone.com/blog/aaronbertrand/sql-server-2016-json-support). – Aaron Bertrand May 21 '22 at 17:16
For example, if you're using `reader[0]` in your C# code, then you won't see a problem until your resulting string exceeds 2033 characters (because at that point, the result will break across multiple rows, and `reader[0]` will just contain _part_ of the data). – Aaron Bertrand May 21 '22 at 17:26
I don't use reader, I use `var scalar = await command.ExecuteScalarAsync();`. - The issue is resolved by declaring unlimited @JSON in the query. TXS ox – Alexandra Danith Ansley May 21 '22 at 17:47
@AlexandraDanithAnsley I was just using reader as an example, the effect is the same. If you try to assign multiple rows to a scalar you're going to get one arbitrary row. – Aaron Bertrand May 21 '22 at 17:52
1

`SELECT (SELECT ... FOR JSON AUTO)` also works. Also @MartinSmith points out where 2033 comes from: seems to be a holdover from SQL2000 and the TDS protocol https://stackoverflow.com/a/33961227/14868997 – Charlieface May 22 '22 at 02:59

Issue where clause FOR JSON AUTO has generated the incomplete answer

1 Answers1