JSON formatting output nested array

Question

Post reply

JSON formatting output nested array

lawson85

SSC Veteran

Points: 202
More actions
January 22, 2023 at 10:56 am

#4140476

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.
Query===========
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,
(select
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
FOR JSON PATH,INCLUDE_NULL_VALUES,ROOT('results')) as JSONPayLoad
from
[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.
Thanks,

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

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.

Steve Collins SSCrazy Points: 2077 More actions · Answer 2

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:

[{"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"}]

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

kaj SSCommitted Points: 1580 More actions · Answer 3

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).