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'
}
]
}
Db. collection. aggregate () can use several channels at the same time for data processing.
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.
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"
}}
]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With