how to use populate and aggregate in same statemen

问题:

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

回答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.

回答2:

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

回答3:

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

回答4:

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

回答5:

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

回答6:

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 }