MongoDb: aggregation $lookup with filtering over the foreign documents

Question

Given a couple of collecitons:

1.- USERS

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

2.- PETS

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

I'm trying to use aggregation with $lookup to build a set of objects that represent users with their pets:

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

But I'd like to add a filter so that only pets older than 1 year are added to each user (using field age inside the pet objects).

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

Actually I'm also trying to get only the oldest of the pets of each user in the same way and I also didn't find the formula.

Any way to perform this action within the aggregation?

Of course, currently I'm doing it afterwards, on the returned objects.

Thanks in advance.

Answer 1

You can use $filter array aggregation operator on pets array that is produced by your $lookup stage.

To output pets older than 1 year 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 output the oldest pets simply replace cond field of $filter operator in the previous aggregation pipeline with

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