Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongo $lookup with multiple $match or $pipeline conditions

I have two mongo collections one containing contacts aka patients and the other containing notifications. I'm attempting to return results with all active contacts status: true in a given branchId / clinic_id and include their acknowledged: false notifications. Also, I want to make sure the contacts show up in the results regardless if they have notifications or not.

I need to 'join' the collections by branchId aka clinic_id and then create an array of each active contacts unacknowledged notifications.

Contact Schema:

{
  patientId: { type: String, required: true },
  clinic_id: { type: String, required: true },
  firstName: { type: String, uppercase: true, required: true },
  lastName: { type: String, uppercase: true, required: true },
  birthDate: { type: String },
  phone: { type: String, required: true },
  status: { type: Boolean, default: true }, // true - active patient
}

Notification Schema:

{
  branchId: { type: String, required: true }, // aka clinic_id
  patientId: { type: String, required: true },
  type: { type: String, required: true }, // sms, chat, system
  acknowledged: { type: Boolean, default: false },
  date: { type: Date, default: Date.now }
}

Aggregation Query:

[
  {
    $match: { clinic_id: '2', status: { $eq: true } }
  },
  {
    $lookup: {
      from: 'notifications',
      let: { patient: '$patientId' },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: ['$acknowleged', false] },
                { $eq: ['$patientId', '$$patientId'] }
              ]
            }
          }
        }
      ],
      as: 'notif'
    }
  }
]

SO FAR THIS SEEMS TO PROVIDE THE BEST RESULTS - I've been using this and then filtering the results in node:

[ 
  { $match: { clinic_id: '2', status: { $eq: true } } },
  { "$lookup": {
    "from": "notifications",
    "let": { "patientId": "$patientId" },
    "pipeline": [
      { "$match": {
        "$expr": { "$eq": ["$branchId", "2"] },
        "acknowledged": { "$eq": false }
      }}
    ],
    "as": "notif"
  }}
]

My problem is that I either get the correct correct contacts but with entries in the notif array from patients with the same patient Id but assigned to another branchId. Or depending on what I've tried, I may get the correct notif array correct, but the initial list of contacts will be missing entries. What is the best way to get the output I need ?

Example Output with comments on desired output and incorrect output I've experienced:

{
  patientId: 1,
  clinic_id: 100,
  firstName: 'John',
  lastName: 'Doe',
  birthDate: '2000-01-01',
  phone: '6665551212',
  status: true,
  notif: [
  {
    // This is correct
    branchId: 100, // branchId matches
    patientId: 1,  // patientId matches contacts patientId
    type: 'sms',
    acknowledged: false, // notification is unacknowledged
    date: '2019-05-18T16:18:05.480Z'
  },
  {
   // This is not correct
    branchId: 200, // branchId does not match contacts branchId
    patientId: 1,
    type: 'sms',
    acknowledged: true, // only want acknowledged: false
    date: '2019-05-20T16:18:05.480Z'
  },
  {
   // This is not correct
    branchId: 100, 
    patientId: 2, // PatientId does not match contact
    type: 'sms', 
    acknowledged: false,
    date: '2019-05-20T16:18:05.480Z'
  }
  ]
}
like image 269
Andrew Taylor Avatar asked May 24 '19 20:05

Andrew Taylor


People also ask

Can we specify more than one aggregate function simultaneously in MongoDB?

Db. collection. aggregate () can use several channels at the same time for data processing.

Can we use multiple group in MongoDB?

Yes the listings have more than two $group stages, the heavy lifting is actually done in two groupings with the others just there for array manipulation if you require it, but it gives you exact and ordered results.


1 Answers

You need to add one more condition with the clinicId. Something like this

[
  { "$match": { "clinic_id": "2", "status": { "$eq": true }}},
  { "$lookup": {
    "from": "notifications",
    "let": { "patient": "$patientId", "clinic_id": "$clinic_id" },
    "pipeline": [
      { "$match": {
        "$expr": {
          "$and": [
            { "$eq": ["$patientId", "$$patientId"] },
            { "$eq": ["$branchId", "$$clinic_id"] }
          ]
        },
        "acknowleged": false
      }}
    ],
    "as": "notif"
  }}
]
like image 163
Ashh Avatar answered Oct 01 '22 14:10

Ashh