Trying to figure out how to make child array with JSON Path Expressions

Question

Need guidance on how to make EmailId a child array of EventId with JSON Path Expressions using the following schema:

http://sqlfiddle.com/#!6/8f8ecc/2

Answer 1

You can use this.

DECLARE @PatientId INT = 1

SELECT Event.*, RE.EmailId 
FROM ( SELECT DISTINCT
          E.EventId,
          E.StartDate,
          E.EndDate,
          E.IsRecurring,
          MRP.RecurringPatternId,
          MRP.MedicationId,
          PMR.PatientId
      FROM 
        Event E
        INNER JOIN MedicationRecurringPattern MRP ON E.EventId = MRP.EventId
        INNER JOIN PatientMedicationReminders PMR ON E.EventId = PMR.EventId
      WHERE 
        PMR.PatientId = @PatientId
    ) Event
  LEFT JOIN ReminderEmails RE ON Event.EventId = RE.EVentId
  LEFT JOIN Email ON RE.EmailId = Email.EmailId
 FOR JSON AUTO