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

Consider changing the accepted answer to ruffrey's answer as Mongoose now supports this. — JohnnyHK, Sep 25 '15 at 22:01

score 124 · Accepted Answer · edited Nov 23 '14 at 15:39

124

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.

edited Nov 23 '14 at 15:39

c1moore

1,827
17
27

answered Apr 17 '14 at 20:07

ruffrey

6,018
3
23
19

i have embedded array of document and how to provide the value from that document to the path in above answer. Document is somewhat like this [{"from" : "objectId", "text" : "This is testing","date" : "date"}] i want to give the path : from in above answer. – Saurabh Ghewari Sep 30 '14 at 14:55
1

See the original question on this - `"patient"` is one of the keys for the documents in an array. In your example, assume `Message` is a mongoose model. `Message.populate(result, {paths: "from"}, callback);` – ruffrey Sep 30 '14 at 22:08
4

To populate 2 paths simple add the to the paths list Message.populate(result, {paths: "path1 path2 path3"}, callback); – Kennedy Nyaga Aug 10 '15 at 20:29
but when I do this I got an empty string.What may be the reason? – santhosh Dec 30 '15 at 17:42
this not answer original question, "same statement". – Ninja Coding May 11 '18 at 17:16
1

so this means I should aggregate first then populate the result, cant I aggregate inside the populate ? – Abdullah Alkurdi Jun 22 '19 at 16:03
2

@KennedyNyaga How can we combine multiple paths along with "select" options for each path? – Satyam Sep 09 '20 at 12:44
1

how can I use deep populate? – Naeem Jan 13 '21 at 10:08
To project some fields in the populated result do `Patients.populate(result, {path: "patient", select: ['name', 'age']}, callback);` – Pranaya Tomar May 20 '22 at 11:57
Good answer !!!! but please note that it results in TWO queries to db; which somewhat defeats the purpose of populate in some way. Kindly use $lookup with aggregation – Pawan Saxena Jun 11 '22 at 17:55

Lotus · Answer 2 · 2021-06-30T14:48:23.783

68

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);
    }
  );

edited Jun 30 '21 at 14:48

answered Mar 15 '16 at 20:50

Lotus

2,596
1
22
20

4

Excellent, sometimes the best answer is the most simple of all, I didn't know that the lookup command take the results of the match that was my confusion. – shontauro Sep 15 '17 at 21:53
2

is this possible to add ```select``` to exclude some fields here? – Vahid Alimohamadi Apr 02 '20 at 15:12
1

@VahidAlimohamadi I am not very sure.. But I think rather than exclude you should try projecting the fields you want. – webcoder Apr 12 '20 at 11:29

score 31 · Answer 3 · answered Sep 24 '19 at 07:15

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}});

score 26 · Answer 4 · edited Oct 28 '17 at 07:33

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

score 20 · Answer 5 · edited Jun 23 '16 at 07:59

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

score 14 · Answer 6 · answered May 21 '13 at 22:26

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;
  });
});

The answer below provides a little easier way of accomplishing the task. No need to write your own loop. — pehaada, Aug 07 '14 at 14:42

score 4 · Answer 7 · edited Jan 12 '17 at 17:24

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 }

How to check if "SelfAssessment.actions" is not empty in "options"? — Ali Mohyudin, Jun 20 '18 at 00:33

score 4 · Answer 8 · answered Sep 11 '21 at 05:20

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

score 0 · Answer 9 · answered Mar 11 '21 at 09:44

I used lookup instead, and it worked well. See the code snipped below.

Post.aggregate([
        {
            $group: {
                // Each `_id` must be unique, so if there are multiple
                // posts with the same category, MongoDB will increment `count`.
                _id: '$category',
                count: { $sum: 1 }
            }
        },
        //from: is collection name in MongoDB, localField are primary and foreign keys in Model.
        {$lookup: {from: 'categories', localField: '_id', foreignField:'_id', as: 'category'}}
    ]).then(categoryCount => {
        console.log(categoryCount);
        let json = [];
        categoryCount.forEach(cat => {
            console.log(json);
        });

how to use populate and aggregate in same statement?

9 Answers9

Old answer below

Linked