SQL Server-"for json path"语句返回的JSON字符串不超过2984行 [英] SQL Server - "for json path" statement does not return more than 2984 lines of JSON string

问题描述

我正在尝试使用"for json path"在复杂且嵌套的JSON字符串中生成大量数据.语句，并且我正在使用多个函数来创建此JSON字符串的不同部分，如下所示:

I'm trying to generate huge amount of data in a complex and nested JSON string using "for json path" statement, and I'm using multiple functions to create different parts of this JSON string, as follow:

declare @queue nvarchar(max)

select @queue = (
    select x.ID as layoutID
        , l.Title as layoutName
        , JSON_QUERY(queue_objects (@productID, x.ID)) as [objects]
    from Layouts x
    inner join LayoutLanguages l on l.LayoutID = x.ID
    where x.ID = @layoutid
    group by x.ID, l.Title
    for json path
)

select @queue as JSON

到目前为止，JSON将是:

Thus far, JSON would be:

{
    "root": [{
        "layouts": [{
            "layoutID": 5
            , "layoutName": "foo"
            , "objects": []
        }]
    }]
}

和"queue_objects"然后将调用函数来填充对象"数组:

and the "queue_objects" function then would be called to fill out 'objects' array:

select 0 as objectID
    , case when (select inherited_counter(@layoutID,0)) > 0 then 'false' else 'true' end as editable
    , JSON_QUERY(queue_properties (p.Table2ID)) as propertyObjects
    , JSON_QUERY('[]') as inherited
from productList p
where p.Table1ID = @productID
group by p.Table2ID 
for json path

然后JSON将是:

{
    "root": [{
        "layouts": [{
            "layoutID": 5
            , "layoutName": "foo"
            , "objects": [{
                "objectID": 1000
                , "editable": "true"
                , "propertyObjects": []
                , "inherited": []
            }, {
                "objectID": 2000
                , "editable": "false"
                , "propertyObjects": []
                , "inherited": []
            }]
        }]
    }]
}

也为"inherited_counter"；和"queue_properties"函数将被调用以填充相应的键.

Also "inherited_counter" and "queue_properties" functions would be called to fill corresponding keys.

这只是一个示例，由于我不在此处放置函数，因此代码无法正常工作.
但是我的问题是:是同时调用函数，使服务器返回损坏的JSON字符串吗?还是服务器本身不能处理超过2984行的JSON字符串?

This is just a sample, the code won't work as I'm not putting functions here.
But my question is: is it the functions that simultaneously call each other, makes the server return broken JSON string? or it's the server itself that can't handle JSON strings more than 2984 lines?

我的意思是2984行，是我在JSON上使用了beautifier，服务器不会逐行返回字符串，而是返回JSON破损的字符串，但是经过美化后，它恰好是2984行字符串. /p>

what I mean by 2984 lines, is that I use beautifier on JSON, the server won't return the string line by line, it returns JSON broken, but after beautifying it happens to be 2984 lines of string.

推荐答案

正如我在对OP的评论中所写，这可能是由于SSMS限制了在结果网格的一列中显示多少个字符.它对实际结果没有影响，例如结果包含所有数据，只是SSMS不能全部显示.

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.

要解决此问题，您可以增加SSMS检索的字符数:

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

我不建议-一段字符串有多长"，而是将结果选择到nvarchar(max)变量和PRINT该变量中.那应该给你全文.

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.

希望这会有所帮助！

这篇关于SQL Server-"for json path"语句返回的JSON字符串不超过2984行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！