Hello there,
I have a collection reports as follows:
{
"_id": { "$oid": "5f05e1d13e0f6637739e215b" },
"testReport": [
{
"name": "Calcium",
"value": "87",
"slug": "ca",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
},
{
"name": "Magnesium",
"value": "-98",
"slug": "mg",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
}
],
"anotherTestReport": [
{
"name": "Calcium",
"value": "-60",
"slug": "ca",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
},
{
"name": "Magnesium",
"value": "80",
"slug": "mg",
"details": {
"description": "description....",
"recommendation": "recommendation....",
"isNormal": false
}
}
],
"patientName": "Patient Name",
"clinicName": "Clinic",
"gender": "Male",
"bloodGroup": "A",
"createdAt": { "$date": "2020-07-08T15:10:09.612Z" },
"updatedAt": { "$date": "2020-07-08T15:10:09.612Z" }
}
and another collection setups
{
"_id": { "$oid": "5efcba7503f4693d164e651d" },
"code": "Ca",
"codeLower": "ca",
"name": "Calcium",
"valueFrom": -75,
"valueTo": -51,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7503f4693d164e651e" },
"code": "Ca",
"codeLower": "ca",
"name": "Calcium",
"valueFrom": 76,
"valueTo": 100,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7603f4693d164e65bb" },
"code": "Mg",
"codeLower": "mg",
"name": "Magnesium",
"valueFrom": -100,
"valueTo": -76,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
},
{
"_id": { "$oid": "5efcba7503f4693d164e6550" },
"code": "Mg",
"codeLower": "mg",
"name": "Magnesium",
"valueFrom": 76,
"valueTo": 100,
"treatmentDescription": "description...",
"isNormal": false,
"gender": "",
"recommendation": "recommendation...",
"createdAt": { "$date": "2020-07-01T16:31:50.205Z" },
"updatedAt": { "$date": "2020-07-01T16:31:50.205Z" }
}
I wanted to search the value from reports collection and check whether the value is in range from the setups collection and return the _id and add the returned _ids in setupIds field on reports collection.
I tried with the following query:
db.reports.aggregate([
{
$match: {
_id: ObjectId("5f05e1d13e0f6637739e215b")
}
},
{
$unwind: {
path: "$testReport"
}
},
{
$lookup: {
from: "setup",
"let": {
testValue: {
$toInt: "$testReport.value"
},
testName: "$testReport.name",
},
pipeline: [
{
$match: {
$expr: {
$and: [{
"$eq": [
"$name",
"$$testName"
]
},
{
"$gte": [
"$valueTo",
"$$testValue"
]
},
{
"$lte": [
"$valueFrom",
"$$testValue"
]
}
]
}
}
}
],
as: "setupIds"
}
},
{
$group: {
_id: "$_id",
patientName: {
$first: "$patientName"
},
clinicName: {
$first: "$clinicName"
},
gender: {
$first: "$gender"
},
bloodGroup: {
$first: "$bloodGroup"
},
createdAt: {
$first: "$createdAt"
},
updatedAt: {
$first: "$updatedAt"
},
setupIds: {
$addToSet: "$setupIds._id"
}
}
},
{
$addFields: {
setupIds: {
$reduce: {
input: "$setupIds",
initialValue: [],
"in": {
$setUnion: [
"$$this",
"$$value"
]
}
}
}
}
}
{ $merge: { into: "updatedReports" } },
])
It’s working as expected. A new collection is added with a field setupIds. I again tried to run the same query but replacing the testReport with anotherTestReport on $unwind and $lookup, hoping new ids will be appended in setupIds. Instead of appending, it replaced the previous ids.
Is there any way that the new values will be appended?
Thanks.