I have a collection of patients, each patient has an array of subdocuments of treatments.
each treatment has an array of diagnoses (which are simple string)
I want to get all the patients, without the treatments (but with an array of all their diagnoses !)
I also would like to get the diagnoses array without duplicate (distinct)
[
{
"_id": "5e517d80da6c3746d416f918",
"firstName": "israel",
"lastName": "k",
"maritalStatus": "Single",
"createdBy": "5e4d918ba81c963ed8d51d25",
"createdAt": "2020-02-22T19:14:08.152Z",
"__v": 1,
"lastTreatment": "2022-03-08T20:29:25.779Z",
"treatments": [
{
"diagnoses": [
"a",
"b"
],
"_id": "5e6555a51567d032640a72f7",
"visitReason": "balba",
"treatmentNumber": 1,
"createdBy": "5e4d918ba81c963ed8d51d25",
"treatmentDate": "2022-03-08T20:29:25.779Z"
},
{
"diagnoses": [
"a",
"c"
],
"_id": "5e6555a51567d032640a72f7",
"visitReason": "blabla",
"treatmentNumber": 2,
"createdBy": "5e4d918ba81c963ed8d51d25",
"treatmentDate": "2022-03-08T20:29:25.779Z"
}
]
},
]
[
{
"_id": "5e517d80da6c3746d416f918",
"firstName": "israel",
"lastName": "k",
"maritalStatus": "Single",
"createdBy": "5e4d918ba81c963ed8d51d25",
"createdAt": "2020-02-22T19:14:08.152Z",
"__v": 1,
"lastTreatment": "2022-03-08T20:29:25.779Z",
"diagnoses": ["a","b","c"]
},
]
How can I do it with MongoDB aggregate framework?
You can run $max to get lastTreatment
and $reduce along with $setUnion to get unique diagnoses:
db.collection.aggregate([
{
$project: {
_id: 1,
firstName: 1,
lastName: 1,
maritalStatus: 1,
createdBy: 1,
createdAt: 1,
__v: 1,
lastTreatment: { $max: "$treatments.treatmentDate" },
diagnoses: {
$reduce: {
input: "$treatments",
initialValue: [],
in: { $setUnion: [ "$$value", "$$this.diagnoses" ] }
}
}
}
}
])