question

MichaelPine-4329 avatar image
0 Votes"
MichaelPine-4329 asked EchoLiu-msft edited

FOR JSON and specifying the property level on the output

I want to return a whole bunch of data from Sql as JSON and I almost have it as I want it but just having trouble with the level or where the child array propertys sit on the output.

 with Files as (
     select 
         fileType.Name FileType
         , fileLocationType.Name FileLocation
         , af.FileUrl
     from AssessmentData ad
     inner join AssessmentFile af on ad.Id = af.AssessmentDataId
     inner join Reference fileType on af.FileTypeId = fileType.Id
     inner join Reference fileLocationType on af.FileLocationTypeId = fileLocationType.Id
     where AssessmentId = AssessmentId
 ), Sensors as (
     select s.SensorId
     from AssessmentData ad
     inner join Sensor s on ad.SensorId = s.Id
     where AssessmentId = AssessmentId
 )
 select
     a.AssessmentId 
     , Files.FileType
     , Files.FileLocation 
     , Files.FileUrl 
     , Sensors.SensorId 
     from Assessment a, Files, Sensors    FOR JSON AUTO    


This gives me just about everything I want but I want the "Sensors" to be a Sibling to "Files"" not a child of "Files"

 [{"AssessmentId":"0B1BDCC9-B47B-4CA3-B638-49C59B41F912","Files":[{"FileType":"Raw","FileLocation":"Azure","FileUrl":"https:\/\/xxxxxxxx.storage.azure.net\/","Sensors":[{"SensorId":"43AA38B4-4EA7-4216-AFC3-56D9309C74EB"},{"SensorId":"43AA38B4-4EA7-4216-AFC3-56D9309C74EB"},{"SensorId":"43AA38B4-4EA7-4216-AFC3-56D9309C74EB"},{"SensorId":"43AA38B4-4EA7-4216-AFC3-56D9309C74EB"}]}]}]

Hopefully that makes sense and hopefully it is possible.

thanks
Michael

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Try something like this:

 with . . .
 select
      a.AssessmentId,
      (select * from Files for json auto) as Files,
      (select * from Sensors for json auto) as Sensors
 from Assessment a
 for json path
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MichaelPine-4329,

To format the output of the FOR JSON clause automatically based on the structure of the SELECT statement, specify the AUTO option.

When you specify the AUTO option, the format of the JSON output is automatically determined based on the order of columns in the SELECT list and their source tables. You can't change this format.

The alternative is to use the PATH option to maintain control over the output.

For more info about the PATH option, see Format Nested JSON Output with PATH Mode.
For an overview of both options, see Format Query Results as JSON with FOR JSON.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.