How to increase JSON code length in grid in SQL Server?-CodePudding

In SQL server I have a select * query, which gives me 2 columns, one is a number and the other is JSON code generated by a sub query using (FOR JSON PATH). The problem is the sub query generates a lot of records which increases the JSON length to 65535 chars (when I copy from the resulting grid). And the JSON code gets truncated and invalid. How can I increase this to be bigger? In SSMS, I tried going into settings and query results > SQL Server > results to grid and changed "non xml data" to 500000. But when I copy it, it is still truncating to 65535 chars.

Does anyone know how to fix this?

CodePudding user response：

You are experiencing this because SQL Server only identifies the returned resultset as JSON if it is a single JSON object as the output.

As you have additional columns, it is only able to represent the JSON object as the nvarchar(max) representation of it and hence you are then constrained by the column size restrictions you identified.

For example, using my table of numbers, if I run:

SELECT Number as [Order.Number],
       DATEADD(SECOND, ROUND((1998 * RAND()   1), 0) , '2022-03-20T08:21:00') as [Order.Date]
FROM dbo.Numbers
FOR JSON PATH, ROOT('Orders')

Then as expected I will see the option to click through to the full JSON object in a separate output page:

If I then use this as a sub-query and include other columns as you have described:

SELECT RAND() as SomeNumber,
(
    SELECT Number as [Order.Number],
           DATEADD(SECOND, ROUND((1998 * RAND()   1), 0) , '2022-03-20T08:21:00') as [Order.Date]
    FROM dbo.Numbers
    FOR JSON PATH, ROOT('Orders')
) as MyJSON

I then see that SSMS is unable to present the single JSON object and instead returns the JSON column as text constrained by the column size limit:

This is an SSMS display restriction - your application should be able to consume the full data from the column.

CodePudding user response：

First declare json nvarchar(max) or varchar(max) then select it. This worked well for me. for example create scalar function to generate json like this:

CREATE FUNCTION dbo.function1 ()
RETURNS NVARCHAR(MAX)
AS
BEGIN
  DECLARE @result NVARCHAR(MAX) = (SELECT
      *
    FROM table
    FOR JSON PATH)
  RETURN @result
END
GO

then use this function every where you want