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'
}
]
}
- 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
1 Answer
Reset to default 5You 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"
}}
]