CODEWITHSUNDEEP
mongodbmongooseaggregation-framework
Tushar Khatiwada
Tushar Khatiwada

Reputation: 2027

mongodb aggregate lookup with a query

I have collections with following values:

reports

{
    "_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
        }
      }
    ],
    "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" }
  },

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" }, // <=== should find this for Calcium
    "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" },  // <=== should find this for Magnesium
    "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 want 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 aggregation framework:

db.reports.aggegrate([
    {
      '$match': {
        '_id': new ObjectId('5f05e1d13e0f6637739e215b')
      }
    }, {
      '$lookup': {
        'from': 'setups', 
        'let': {
          'testValue': '$testReport.value', 
          'testName': '$testReport.name'
        }, 
        'pipeline': [
          {
            '$match': {
              '$expr': {
                {
                    '$and': [
                      {
                        '$eq': [
                          '$name', '$$testName'
                        ]
                      }, {
                        '$gte': [
                          '$valueTo', '$$testValue'
                        ]
                      }, {
                        '$lte': [
                          '$valueFrom', '$$testValue'
                        ]
                      }
                    ]
                  }
              }
            }
          }
        ], 
        'as': 'setupIds'
      }
    }
  ])

This query didn't find the expected results.

This is the updated reports collection I want:

{
    "_id": { "$oid": "5f05e1d13e0f6637739e215b" },
    "setupIds": [{ "$oid": "5efcba7503f4693d164e651e" }, { "$oid": "5efcba7603f4693d164e65bb" }],  // <=== Here, array of the ObjectId (ref: "Setups")
    "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" }
  },

Upvotes: 2

Views: 83

Answers (1)

varman
varman

Reputation: 8894

You can try like following

[{
    $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"]
                }
            }


        }
    }
}]

Working Mongo playground

Upvotes: 2

Related Questions