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