7

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

Thus far, JSON would be:

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

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

queue_objects

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

And then JSON would be:

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

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

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?

EDIT: 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.

Community
  • 1
  • 1
Brian Salehi
  • 414
  • 2
  • 10
  • 19
  • When you get your 2984 lines - are you running this in SSMS? Why I'm asking is as SSMS has a limitation on how much it returns when you do a SELECT: "Tools | Options | Query Results | SQL Server | Results to Grid | Maximum characters retrieved." – Niels Berglund Feb 03 '18 at 12:29
  • Yes I do run my queries on SSMS, I think that should be the answer! so how can I increase the amount of data I can get? and does this whole story affect the results I get from webpage? the page that queries database to get results? – Brian Salehi Feb 03 '18 at 13:09

1 Answers1

5

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:

SSMS Display Result

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!

Niels Berglund
  • 1,713
  • 8
  • 6
  • 1
    this is correct, SSMS would only return a limited amount of data, even if I put it in a variable and then print or select it. But when I get this data in production environment, it is complete. – Brian Salehi Mar 06 '18 at 11:01
  • 1
    It seems you cannot increase the "Non XML data" value above 65535. :( – youcantryreachingme Jul 16 '19 at 03:42
  • 1
    I tried the recommendation with the NVARCHAR(MAX) and PRINT, and it was truncated the same as it was in the result grid. – Milton Oct 23 '20 at 02:07
  • Any update on this? I'm experiencing this problem using the tedious.js driver. Seems not to be an SSMS issue. – Colin Dec 01 '21 at 20:00