Sql Server - "For JSON Path" Statement Does Not Return More Than 2984 Lines of JSON String

SQL Server - for json path statement does not return more than 2984 lines of JSON string

As I wrote in my comment to the OP, this is probably due to SSMS has a limit of how many characters to display in a column in the result grid. It has no impact on the actual result, e.g. the result has all data, it is just that SSMS doesn't display it all.

To fix this, you can increase the number of characters SSMS retrieves:

I would not recommend that - "how long is a piece of string", but instead select the result into a nvarchar(max) variable, and PRINT that variable. That should give you the whole text.

Hope this helps!

Convert result into doubly nested JSON format

You can use the following code:

• Inside an APPLY, unpivot the columns as key/value pairs...
• ... and aggregate using FOR JSON PATH
• Use STRING_AGG to do another aggregation.

SELECT '[' + STRING_AGG(CAST(v.json AS nvarchar(max)), ',') + ']'
FROM T
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('ID', CAST(ID AS nvarchar(100))),
        ('Name', Name),
        ('Program', Program),
        ('Type', [Type]),
        ('Section', Section),
        ('Director', Director),
        ('Project', Project),
        ('Sr Manager', [Sr Manager]),
        ('PCM', PCM),
        ('Contractor', Contractor),
        ('Cost Client', [Cost Client])
    ) v([key], value)
    FOR JSON PATH
) v(json)

db<>fiddle

You cannot use FOR JSON again, because then you will get ["json": [{"key" : ...

FOR JSON PATH results in SSMS truncated to 2033 characters

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,'')

FOR JSON PATH returning escaped strings for nested objects

I solved this by eliminating the UNION altogether. In this case, it was as simple as adding an WHERE … OR language.short_name IS NULL to the first query.

SQL Server 2016 FOR JSON PATH returns string instead of array when using case statement

Wrap the result from the case statement in a call to JSON_QUERY.

, [data.array3] = JSON_QUERY(
                            CASE WHEN @id is NOT NULL
                            THEN JSON_QUERY(@projects, '$.projects') 
                            ELSE NULL END
                            )

According to the documentation JSON_QUERY "Extracts an object or an array from a JSON string". Further down it says "Returns a JSON fragment of type nvarchar(max).". A bit confusing.

Doing a for xml json on a string value will give you a string value in the returned JSON string and when you do it on a JSON object you get the JSON object inlined in the resulting string value.

You can look at CASE as a function call with a return value automatically determined for you by looking at what values you are returning from the CASE. And since JSON_QUERY returns a string the case will return a string and the returned value will be a string value in JSON.

The case statement in the query plan looks like this.

<ScalarOperator ScalarString="CASE WHEN [@id] IS NOT NULL THEN json_query([@projects],N'$.projects') ELSE NULL END">

When you wrap the case in a call to JSON_QUERY it looks like this instead.

<ScalarOperator ScalarString="json_query(CASE WHEN [@id] IS NOT NULL THEN json_query([@projects],N'$.projects') ELSE NULL END)">
  <Intrinsic FunctionName="json_query">

By some kind of internal magic SQL Server recognize this as a JSON object instead of a string and inserts it into the resulting JSON string as a JSON value instead.

CASE WHEN 1 is NOT NULL works because SQL Server is smart enough to see that the case statement will always be true and is optimized away.

---

Related Topics