Mongoose virtual populate and aggregates

Question

I'm trying to do aggregations on Mongoose schemas that use the newer virtual populate functionality (using Mongoose 4.13, Mongo 3.6).

Lets say I have the following (simplified for illustration purposes) schemas:

const ProjectSchema = new mongoose.Schema({
  projectId: Number,
  description: String
});

ProjectSchema.virtual('tasks', {
  ref: 'Task',
  localField: 'projectId',
  foreignField: 'projectId' 
  justOne: false
]);

const TaskSchema = new mongoose.Schema({
  taskId: Number,
  projectId: Number
  hours: Number
});

const Project = mongoose.model('Project', ProjectSchema);
const Task = mongoose.model('Task', TaskSchema);

Querying on Project and populating relating tasks is working fine using .populate() like:

Project.find({projectId: <id>}).populate('tasks');

But now I would like to sum the hours on tasks by project (left the $sum part out below btw..). I'm only getting empty arrays back no matter what. Isn't it possible to aggregate with virtual populate or?

const result = await Project.aggregate([
   { $match : { projectId: <id> } },
   { $lookup: { 
       from: 'tasks',
       localField: 'projectId',
       foreignField: 'projectId',
       as: 'tasks'
     },
     {
        $unwind: '$tasks' 
     }
   }
 ]);

score 2 · Answer 1 · answered Dec 15 '17 at 15:13

Referencing mongoose's documentation for Model.aggregate:

Arguments are not cast to the model's schema because $project operators allow redefining the "shape" of the documents at any stage of the pipeline, which may leave documents in an incompatible format.

Essentially this means the magic of anything mongoose allows via using a schema is not applied when using aggregates. In fact, you would need to refer directly to MongoDB's documentation for aggregation (specifically $lookup).

const ProjectSchema = new mongoose.Schema({
  projectId: Number,
  description: String
});

const TaskSchema = new mongoose.Schema({
  taskId: Number,
  projectId: Number,
  hours: Number
});

const Project = connection.model('Project', ProjectSchema);
const Task = connection.model('Task', TaskSchema);

const project1 = new Project({ projectId: 1, description: 'Foo'});
const project2 = new Project({ projectId: 2, description: 'Foo'});
const task1 = new Task({ task: 1, projectId: 1, hours: 1});
const task2 = new Task({ task: 2, projectId: 1, hours: 2});
const task3 = new Task({ task: 3, projectId: 2, hours: 1});

Promise.all([
  project1.save(),
  project2.save(),
  task1.save(),
  task2.save(),
  task3.save()
]).then(() => {
  return Project.aggregate([
    { $match: { projectId: 1 }},
    {
      $lookup: {
        from: 'tasks',
        localField: 'projectId',
        foreignField: 'projectId',
        as: 'tasks'
      }
    }
  ]).exec();
}).then((result) => {
  console.dir(result, { depth: null, color: true });
});

Outputs the following:

[{
  _id: ObjectID {/*ID*/},
  projectId: 1,
  description: 'Foo',
  __v: 0,
  tasks: [{
      _id: ObjectID {/*ID*/},
      projectId: 1,
      hours: 2,
      __v: 0
    },
    {
      _id: ObjectID {/*ID*/},
      projectId: 1,
      hours: 1,
      __v: 0
    }
  ]
}]

But you might ask: "That's basically what I have so why does that work?!"

I am not sure if your example code was copy/pasted but in the example provided the $unwind stage appears to have been accidentally included in the 2nd stage ($lookup) of your pipeline. If you were to add it as a 3rd stage as the following code shows then the output would be altered as shown.

return Project.aggregate([
  { $match: { projectId: 1 }},
  {
    $lookup: {
      from: 'tasks',
      localField: 'projectId',
      foreignField: 'projectId',
      as: 'tasks'
    }
  },
  { $unwind: '$tasks' }
]).exec();

Outputs:

[{
    _id: ObjectID {/*ID*/},
    projectId: 1,
    description: 'Foo',
    __v: 0,
    tasks: {
      _id: ObjectID {/*ID*/},
      projectId: 1,
      hours: 1,
      __v: 0
    }
  },
  {

    _id: ObjectID {/*ID*/},
    projectId: 1,
    description: 'Foo',
    __v: 0,
    tasks: {
      _id: ObjectID {/*ID*/},
      projectId: 1,
      hours: 2,
      __v: 0
    }
  }
]

Thanks, much appreciated. I found the issue I was having. I use custom defined collection names on my schemas to make the collections easier to differentiate. The 'from' field in the $lookup aggregation specifies the collection name, I was trying to referencing the schema name which is not the same in my case. It's working fine now! — Ramon, Dec 21 '17 at 13:33

score 1 · Answer 2 · answered Nov 12 '19 at 13:34

1

mongoose >= 3.6 supports the Model.populate() method.

So, You can pass the result of the aggregate method to the populate method like this.

var opts = [{ path: 'tasks'}];
const populatedResult = await Project.populate(result, opts);

answered Nov 12 '19 at 13:34

ruwan800

1,567
17
21

score 0 · Answer 3 · answered Dec 06 '17 at 10:43

0

Virtual populate won't work with aggregate, because "The documents returned are plain javascript objects, not mongoose documents (since any shape of document can be returned)."(c), more information in the docs - Mongoose aggregate

answered Dec 06 '17 at 10:43

vasylOk

546
4
7

Hmm.. I came along this [SO question](https://stackoverflow.com/questions/46472643/how-to-aggregate-when-using-populate-virtuals-in-mongoose) where is mentioned to use $lookup with virtual populates. Any thoughts on that? – Ramon Dec 06 '17 at 13:19

Mongoose virtual populate and aggregates

3 Answers3