how to use populate and aggregate in same statement?

Question

This is my appointment collection:

{ _id: ObjectId("518ee0bc9be1909012000002"), date: ISODate("2013-05-13T22:00:00Z"), patient:ObjectId("518ee0bc9be1909012000002") }

{ _id: ObjectId("518ee0bc9be1909012000002"), date: ISODate("2013-05-13T22:00:00Z"), patient:ObjectId("518ee0bc9be1909012000002") }

{ _id: ObjectId("518ee0bc9be1909012000002"), date: ISODate("2013-05-13T22:00:00Z"), patient:ObjectId("518ee0bc9be1909012000002") }

I used aggregate to get the following result

{date: ISODate("2013-05-13T22:00:00Z"),
patients:[ObjectId("518ee0bc9be1909012000002"),ObjectId("518ee0bc9be1909012000002"),ObjectId("518ee0bc9be1909012000002")] }

like this:

Appointments.aggregate([
{$group: {_id: '$date', patients: {$push: '$patient'}}},
{$project: {date: '$_id', patients: 1, _id: 0}}
], ...)

How can I populate the patient document I trued this but it doesn't work ... Appointments.find({}).populate("patient").aggregate....

In other words, can i use populate and aggregate at the same statement

any help please

Answer 1

With the latest version of mongoose (mongoose >= 3.6), you can but it requires a second query, and using populate differently. After your aggregation, do this:

Patients.populate(result, {path: "patient"}, callback);

See more at the Mongoose API and the Mongoose docs.

Answer 2

Edit: Looks like there's a new way to do it in the latest Mongoose API (see the above answer here: https://stackoverflow.com/a/23142503/293492)

Old answer below

You can use $lookup which is similar to populate.

In an unrelated example, I use $match to query for records and $lookup to populate a foreign model as a sub-property of these records:

  Invite.aggregate(
      { $match: {interview: req.params.interview}},
      { $lookup: {from: 'users', localField: 'email', foreignField: 'email', as: 'user'} }
    ).exec( function (err, invites) {
      if (err) {
        next(err);
      }

      res.json(invites);
    }
  );

Answer 3

You can do it in one query like this:

Appointments.aggregate([{
        $group: {
            _id: '$date',
            patients: {
                $push: '$patient'
            }
        }
    },
    {
        $project: {
            date: '$_id',
            patients: 1,
            _id: 0
        }
    },
    {
        $lookup: {
            from: "patients",
            localField: "patient",
            foreignField: "_id",
            as: "patient_doc"
        }
    }
])

populate basically uses $lookup under the hood. in this case no need for a second query. for more details check MongoDB aggregation lookup

Answer 4

You have to do it in two, not in one statement.

In async await scenario, make sure await until populate.

const appointments = await Appointments.aggregate([...]);

await Patients.populate(appointments, {path: "patient"});

return appointments;

or (if you want to limit)

await Patients.populate(appointments, {path: "patient",  select:  {_id: 1, fullname: 1}});

Answer 5

Perform a Join with $lookup

A collection orders contains the following documents:

{ "_id" : 1, "item" : "abc", "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1 }
{ "_id" : 3  }

Another collection inventory contains the following documents:

{ "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
{ "_id" : 2, "sku" : "def", description: "product 2", "instock" : 80 }
{ "_id" : 3, "sku" : "ijk", description: "product 3", "instock" : 60 }
{ "_id" : 4, "sku" : "jkl", description: "product 4", "instock" : 70 }
{ "_id" : 5, "sku": null, description: "Incomplete" }
{ "_id" : 6 }

The following aggregation operation on the orders collection joins the documents from orders with the documents from the inventory collection using the fields item from the orders collection and the sku field from the inventory collection:

db.orders.aggregate([
    {
      $lookup:
        {
          from: "inventory",
          localField: "item",
          foreignField: "sku",
          as: "inventory_docs"
        }
   }
])

The operation returns the following documents:

{
  "_id" : 1,
   "item" : "abc",
  "price" : 12,
  "quantity" : 2,
  "inventory_docs" : [
    { "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120       }
  ]
 }
{
  "_id" : 2,
  "item" : "jkl",
  "price" : 20,
  "quantity" : 1,
  "inventory_docs" : [
    { "_id" : 4, "sku" : "jkl", "description" : "product 4", "instock" : 70 }
  ]
}
{
  "_id" : 3,
  "inventory_docs" : [
    { "_id" : 5, "sku" : null, "description" : "Incomplete" },
    { "_id" : 6 }
  ]
}

Reference $lookup

Answer 6

Short answer: You can't.

Long answer: In the Aggregation Framework, the returned fields are built by you, and you're able to "rename" document properties.

What this means is that Mongoose can't identify that your referenced documents will be available in the final result.

The best thing you can do in such a situation is populate the field you want after the query has returned. Yes, that would result in two DB calls, but it's what MongoDB allows us to do.

Somewhat like this:

Appointments.aggregate([ ... ], function( e, result ) {
  if ( e ) return;

  // You would probably have to do some loop here, as probably 'result' is array
  Patients.findOneById( result.patient, function( e, patient ) {
    if ( e ) return;

    result.patient = patient;
  });
});

Answer 7

domain.Farm.aggregate({
    $match: {
        "_id": mongoose.Types.ObjectId(farmId)
    }
}, {
    $unwind: "$SelfAssessment"
}, {
    $match: {
        "SelfAssessment.questionCategoryID": QuesCategoryId,
        "SelfAssessment.questionID": quesId
    }
},function(err, docs) {
      var options = {
          path: 'SelfAssessment.actions',
          model: 'FarmAction'
     };
     domain.Farm.populate(docs, options, function (err, projects) {
       callback(err,projects);

      });

});

results i got action model populate

{   "error": false,   "object": [
    {
      "_id": "57750cf6197f0b5137d259a0",
      "createdAt": "2016-06-30T12:13:42.299Z",
      "updatedAt": "2016-06-30T12:13:42.299Z",
      "farmName": "abb",
      "userId": "57750ce2197f0b5137d2599e",
      "SelfAssessment": {
        "questionName": "Aquatic biodiversity",
        "questionID": "3kGTBsESPeYQoA8ae2Ocoy",
        "questionCategoryID": "5aBe7kuYWIEoyqWCWcAEe0",
        "question": "Waterways protected from nutrient runoff and stock access through fencing, buffer strips and off stream watering points",
        "questionImage": "http://images.contentful.com/vkfoa0gk73be/4pGLv16BziYYSe2ageCK04/6a04041ab3344ec18fb2ecaba3bb26d5/thumb1_home.png",
        "_id": "57750cf6197f0b5137d259a1",
        "actions": [
          {
            "_id": "577512c6af3a87543932e675",
            "createdAt": "2016-06-30T12:38:30.314Z",
            "updatedAt": "2016-06-30T12:38:30.314Z",
            "__v": 0,
            "Evidence": [],
            "setReminder": "",
            "description": "sdsdsd",
            "priority": "High",
            "created": "2016-06-30T12:38:30.312Z",
            "actionTitle": "sdsd"
          }
        ],
        "answer": "Relevant"
      },
      "locations": []
    }   ],   "message": "",   "extendedMessage": "",   "timeStamp": 1467351827979 }

Answer 8

I see that there are many answers, I am new to mongoldb and I would like to share my answer too. I am using aggregate function along with lookup to populate the patients. To make it easy to read I have changed the names of the collections and fields.

Hope it's helpful.

DB:

db={
  "appointmentCol": [
    {
      _id: ObjectId("518ee0bc9be1909012000001"),
      date: ISODate("2013-05-13T22:00:00Z"),
      patientId: ObjectId("518ee0bc9be1909012000001")
    },
    {
      _id: ObjectId("518ee0bc9be1909012000002"),
      date: ISODate("2013-05-13T22:00:00Z"),
      patientId: ObjectId("518ee0bc9be1909012000002")
    },
    {
      _id: ObjectId("518ee0bc9be1909012000003"),
      date: ISODate("2013-05-13T22:00:00Z"),
      patientId: ObjectId("518ee0bc9be1909012000003")
    }
  ],
  "patientCol": [
    {
      "_id": ObjectId("518ee0bc9be1909012000001"),
      "name": "P1"
    },
    {
      "_id": ObjectId("518ee0bc9be1909012000002"),
      "name": "P2"
    },
    {
      "_id": ObjectId("518ee0bc9be1909012000003"),
      "name": "P3"
    },
    
  ]
}

Aggregate Query using lookup:

db.appointmentCol.aggregate([
  {
    "$lookup": {
      "from": "patientCol",
      "localField": "patientId",
      "foreignField": "_id",
      "as": "patient"
    }
  }
]) 

Output:

[
  {
    "_id": ObjectId("518ee0bc9be1909012000001"),
    "date": ISODate("2013-05-13T22:00:00Z"),
    "patient": [
      {
        "_id": ObjectId("518ee0bc9be1909012000001"),
        "name": "P1"
      }
    ],
    "patientId": ObjectId("518ee0bc9be1909012000001")
  },
  {
    "_id": ObjectId("518ee0bc9be1909012000002"),
    "date": ISODate("2013-05-13T22:00:00Z"),
    "patient": [
      {
        "_id": ObjectId("518ee0bc9be1909012000002"),
        "name": "P2"
      }
    ],
    "patientId": ObjectId("518ee0bc9be1909012000002")
  },
  {
    "_id": ObjectId("518ee0bc9be1909012000003"),
    "date": ISODate("2013-05-13T22:00:00Z"),
    "patient": [
      {
        "_id": ObjectId("518ee0bc9be1909012000003"),
        "name": "P3"
      }
    ],
    "patientId": ObjectId("518ee0bc9be1909012000003")
  }
]

Playground: mongoplayground.net