Hello : )
If you need for all staff,their primary patients,look up can do it.
If you need for a specific staff,it could be much more simple without look up.
Maybe there is a better way in general but this does what you want i think.
Mongodb has also $graphLookup but here is only 1 level the relation,so lookup works also.
If you have 1 relation(like reports to) ,and many connections look that also.
Data in (added george also)
[
{
"_id": "alice",
"type": "staff"
},
{
"_id": "john",
"type": "staff"
},
{
"_id": "janice",
"type": "patient",
"careTeam": [
{
"_id": "alice",
"primary": true
},
{
"_id": "john"
}
]
},
{
"_id": "george",
"type": "patient",
"careTeam": [
{
"_id": "alice",
"primary": true
},
{
"_id": "john"
}
]
},
{
"_id": "michael",
"type": "patient",
"careTeam": [
{
"_id": "john",
"primary": true
},
{
"_id": "alice"
}
]
}
]
Query
Left aggregation = keep the staff only
Right aggregation = keep the patients,from the careTeam,keep only the names of the primary care takers(reduce),join only is the staff id is on the array of the primary caretakers,and keep
only the id of that patient.
{
"aggregate": "testcoll",
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
"$type",
"staff"
]
}
}
},
{
"$lookup": {
"from": "testcoll",
"let": {
"staffid": "$_id"
},
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
"$type",
"patient"
]
}
}
},
{
"$addFields": {
"primaryCareTeam": {
"$reduce": {
"input": "$careTeam",
"initialValue": [],
"in": {
"$cond": [
{
"$eq": [
"$$this.primary",
true
]
},
{
"$concatArrays": [
"$$value",
[
"$$this._id"
]
]
},
"$$value"
]
}
}
}
}
},
{
"$match": {
"$expr": {
"$in": [
"$$staffid",
"$primaryCareTeam"
]
}
}
},
{
"$project": {
"_id": 1
}
}
],
"as": "primaryPatients"
}
}
],
"maxTimeMS": 1200000,
"cursor": {}
}
Result
{
"_id": "alice",
"type": "staff",
"primaryPatients": [
{
"_id": "janice"
},
{
"_id": "george"
}
]
},
{
"_id": "john",
"type": "staff",
"primaryPatients": [
{
"_id": "michael"
}
]
}