javascript - Mongo $lookup with multiple $match or $pipeline conditions - Stack Overflow

admin管理员组

文章数量:1391947

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 ments 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'
  }
  ]
}

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 ments 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'
  }
  ]
}

• javascript
• node.js
• mongodb
• mongoose
• aggregation-framework

Share Improve this question Follow edited May 25, 2019 at 2:40 Andrew Taylor asked May 24, 2019 at 20:58 Andrew TaylorAndrew Taylor 62877 silver badges2929 bronze badges 1

• In case anyone is following the question through there is a slight spelling error on the original aggregation query. "acknowleged" should be "acknowledged" of course. It wasn't enough characters for me to edit_ – jesus g_force Harris Commented Nov 10, 2021 at 10:52

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

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"
  }}
]

本文标签： javascriptMongo lookup with multiple match or pipeline conditionsStack Overflow