How to create JSON Array Inside JSON object using FOR JSON SQL Server 2016

Question

How to create JSON Array Inside JSON object using FOR JSON SQL Server 2016 (TABLE to JSON)
Here is my query:

SELECT   
m.MeetingId AS tblMeeting_MeetingId,  
m.Attended AS tblMeeting_Attended, 
m3.CompanyId AS tblMeetingAttendants_CompanyId, 
m3.MeetingAttendantsId AS tblMeetingAttendants_AttendantNameWithTitle, 
m4.UserId AS tblMeetingAttendees_UserId, 
m5.BrokerId AS tblMeetingBroker_BrokerId 
FROM Bv.tblMeeting m 
LEFT JOIN Bv.tblMeetingAttendants m3 ON m.MeetingId = m3.MeetingId  
LEFT JOIN Bv.tblMeetingAttendees m4 ON m.MeetingId = m4.MeetingId  
LEFT JOIN Bv.tblMeetingBroker m5 ON m.MeetingId = m5.MeetingId  
WHERE m.MeetingId = 739 
FOR JSON AUTO, INCLUDE_NULL_VALUES

Above query gives me result like this:

[
{
    "tblMeeting_MeetingId": 739,
    "tblMeeting_Attended": false,
    "tblMeeting_MeetingSubject": " Benchmark China Internet Analyst",
    "m3": [
        {
            "tblMeetingAttendants_CompanyId": 83,
            "tblMeetingAttendants_AttendantNameWithTitle": 499,
            "m4": [
                {
                    "tblMeetingAttendees_UserId": null,
                    "m5": [
                        {
                            "tblMeetingBroker_BrokerId": 275
                        }
                    ]
                }
            ]
        },
        {
            "tblMeetingAttendants_CompanyId": 83,
            "tblMeetingAttendants_AttendantNameWithTitle": 500,
            "m4": [
                {
                    "tblMeetingAttendees_UserId": null,
                    "m5": [
                        {
                            "tblMeetingBroker_BrokerId": 275
                        }
                    ]
                }
            ]
        },
        {
            "tblMeetingAttendants_CompanyId": 83,
            "tblMeetingAttendants_AttendantNameWithTitle": 501,
            "m4": [
                {
                    "tblMeetingAttendees_UserId": null,
                    "m5": [
                        {
                            "tblMeetingBroker_BrokerId": 275
                        }
                    ]
                }
            ]
        }
    ]
}
]

But i want result like this

[
{
    "tblMeeting_MeetingId": 739,
    "tblMeeting_Attended": false,
    "tblMeeting_MeetingSubject": " Benchmark China Internet Analyst",
    "tblMeetingAttendants_AttendantNameWithTitle": [499,500,501],
    "tblMeetingAttendees_UserId": null,
    "tblMeetingBroker_BrokerId": 275
}
]

Please reply as soon as possible
Thanks in advance.

Answer 1

Can use JSON_QUERY with JSON PATH to format your data into a JSON array. I sampled just your MeetingID and MeetingAttendantID columns to demonstrate the concept

Build JSON Array using JSON_QUERY

DROP TABLE IF EXISTS #MeetingAttendance

CREATE TABLE #MeetingAttendance (MeetingID INT,AttendantID INT)
INSERT INTO #MeetingAttendance
VALUES (739,499)
,(739,500)
,(739,501)

SELECT tblMeeting_MeetingId = MeetingID
    ,tblMeetingAttendants_AttendantNameWithTitle = JSON_QUERY('['+STRING_AGG(CONCAT('"',AttendantID,'"'),',') + ']')   
FROM #MeetingAttendance
GROUP BY MeetingID
FOR JSON PATH,WITHOUT_ARRAY_WRAPPER 

Results

{
    "tblMeeting_MeetingId": 739,
    "tblMeetingAttendants_AttendantNameWithTitle": [
        "499",
        "500",
        "501"
    ]
}

Answer 2

It seems like this is impossible without using string concatenation and writing your own functions. There is no magic JSON_ARRAY_AGGREGATE() function. I have been looking for one myself. Here is a related question: SQL Server 2016 for JSON output integer array