FOR JSON command adding carriage return to output

  • I'm pretty new a creating JSON output form SQL server. I'm having trouble with carriage returns being placed at inappropriate positions in the output when saving the results as a JSON file. Is there a setting or adjustment needed in my SSMS environment?

    The data was created using the methods form blog post: https://www.sqlservercentral.com/blogs/how-to-create-multi-object-json-arrays-in-sql-server-2.

    Output SQL:

    SELECT 
    npi
    ,type
    ,name = JSON_QUERY(name, '$')
    ,facility_name
    ,facility_type = JSON_QUERY(facility_type, '$')
    ,addresses = JSON_QUERY(addresses, '$')
    ,specialty = JSON_QUERY(specialty, '$')
    ,accepting
    ,last_updated_on
    ,plans = JSON_QUERY(plans, '$')
    FROM #Provjson
    FOR JSON PATH

    The sample output data test JSON file attached.

    Thank you in advance to any help.

     

    • This topic was modified 1 month, 2 weeks ago by  fxferguson. Reason: File load error
    Attachments:
    You must be logged in to view attached files.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Found this solution on github: https://github.com/MicrosoftDocs/sql-docs/issues/1501

    The problem seems to be that SSMS can't return it all at once properly so instead it simply appends results with a max result size of 2034, adds a line break (for some reason?) and repeats that until it all returns.

    wrapping the select and cast as nvarchar(max) removes the carriage returns. That's odd so it's still a caveat I think that should be noted. Someone would try to copy the results and there could be line breaks in the results because of SSMS returning them unexpectedly throughout the data.

  • I've tested it on multiple strings with different outcomes and cannot figure out how exactly it works and what it does to a string, any help would be appreciated!

    AARP Medicare

    • This reply was modified 1 month, 1 week ago by  ArmandoRohan. Reason: Mistake

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply