JSON formatting output nested array

  • Hi Guys,

    Apologies if this is posted in the wrong forum, but I have a query that moves data from a 'flat' table to a JSON formatted one.


    insert into [SDB].[JSON].[RawJson]([results_incident_OSVC_Incident_ID],[results_incident_OSvC_Refnum],[JsonPayload])

    select b.[results_incident_OSVC_Incident_ID], b.results_incident_OSvC_Refnum,


    a.results_incident_OSVC_Incident_ID as 'incident.IncidentID',

    a.results_incident_OSvC_Refnum as 'incident.RefNum',

    a.results_incident_Learner_OSvC_Learner_ID as 'incident.Learner.LearnerID',

    a.results_incident_subject_SUBJECT_CODE as 'incident.subject.SubjectCode',

    a.results_incident_schools_OSvC_SchoolAppID as 'incident.schools.SchoolAppID',

    a.results_incident_PrimaryParent_OSvC_P_ParentID as 'incident.PrimaryParent.PrimaryParent',

    a.results_incident_Application_OSvC_ApplicationID as 'incident.Application.ApplicationID',

    a.results_incident_SecondaryParent_OSvC_S_ParentID as 'incident.SecondaryParent.SecondaryParent'

    from [SDB].[RAW].[CreateApplication] a

    where a.results_incident_OSVC_Incident_ID = b.results_incident_OSVC_Incident_ID



    [SDB].[RAW].[CreateApplication] b

    order by b.results_incident_OSVC_Incident_ID

    The output format is pretty much what I expect except for where the results have nested arrays, the results are all one one line, I would like to separate the nested arrays onto a new line.




  • We will need your help in order to be able to help you, so please help us!


    It would be greatly appreciated if you could provide the DDL (create table) script, sample data as an insert statement, the desired output from the sample data, and what you have tried so far.

  • Afaik JSON treats carriage returns as white space.  At the bottom of this page it says:

    Whitespace can be inserted between any pair of tokens

    Then there's this chart which seems to suggest space, linefeed, carriage return, and horizontal tab are interpreted as whitespace:

    If you're looking for nicely formatted JSON in SSMS you're SOL afaik lol.  No dark mode, no copy/paste rectangles, and no JSON formatting.  If you switch to Visual Studio, click on a piece of JSON in the results grid, then right click and select 'Format Document' it displays as nicely formatted.  It formats this:


    as this:

    "api_id": 1,
    "pc_id": 37,
    "securitystamp": "A3377307-C493-499D-A18D-9D3309AC4667",
    "created_dt": "2021-12-13T17:03:38.3357396"
    "api_id": 2,
    "pc_id": 1,
    "securitystamp": "D19DF0CC-C5EF-44FE-AA18-E7BB18A762DF",
    "created_dt": "2022-06-22T18:49:25.0035917"
    "api_id": 3,
    "pc_id": 53,
    "securitystamp": "EFE5CE75-5D78-4600-989F-89E88C4A40B5",
    "created_dt": "2022-08-11T14:39:33.1667260"

    Hopefully SSC preserves the formatting in the code window.  It worked in 'Preview' mode

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • SQL Server doesn't support a JSON data type, in contrast to the XML data type which can be employed for storing XML.

    When the XML data type is used for storing, it is shown in SSMS as a clickable blue text, which will be shown in a browser-like feature of SSMS if clicked. SSMS will format the XML in a nicely formattet view.

    The same is not true for JSON, for more than one reason. Firstly there is no JSON data type in SQL Server, secondly a browser will not format JSON, like it will XML; because JSON doesn't support a schema transformation like XSLT, which is what browsers and the XML editor of SSMS (probably) use to present a nicely indented format of XML (the prettifyer effect).

    • This reply was modified 2 months ago by  kaj. Reason: Removed direct reference to browsers and inserted SSMS instead
  • Thanks for your reply a colleague of mine resolved the issue with ADF.

  • Thank you for your response.

  • Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

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