MongoDb: $ aggregation search with filtering by foreign documents

Question

Given a couple of collections:

1.- USERS

{ name: ... id: ... city: ... age: ... otherdata: ... } 

2.- PETS

 { name: ... owner: ... type: ... age: ... } 

I am trying to use aggregation with $ lookup to create a set of objects that represent users with their pets:

 collectionusers.aggregate([ { $lookup: { from: "pets", localField: "id", foreignField: "owner", as: "pets" } } ]); 

But I would like to add a filter so that each user only adds pets older than 1 year (using the age of the field inside the pets).

The problem is that adding $ match to aggregation does not work because it filters out users without old pets, and I want users to be there even if they don't have pets.

Actually, I also try to get only the oldest of each user's pets, and I also did not find the formula.

Any way to perform this action in aggregation?

Of course, I am currently doing this afterwards, on returned objects.

Thanks in advance.

Answer 1

You can use the $filter array to aggregate the array on the pets array, which is created by your $lookup stage.

For pets over 1 year old, use

 db.users.aggregate([ { $lookup: { from: "pets", localField: "id", foreignField: "owner", as: "pets" } }, { $project: { name: 1, pets: { $filter: { input: "$pets", as: "pet", cond: { $gte: [ "$$pet.age", 1 ] } } } } } ]); 

To display the oldest pets, simply replace the cond field of the $filter operator in the previous aggregation pipeline with

 cond: { $eq: [ "$$pet.age", { $max: "$pets.age" } ] }