试图找出如何让孩子阵列JSON路径表达式

Question

就如何使EMAILID EVENTID的子阵列使用下面的架构JSON路径表达式需要指导：

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

Answer 1

您可以使用此。

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