23

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.

Neil Lunn
  • 148,042
  • 36
  • 346
  • 317
Gabriel
  • 233
  • 1
  • 2
  • 5

1 Answers1

25

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" } ] }
mr.tarsa
  • 6,386
  • 3
  • 25
  • 42
  • Great, thanks. One more think I'm going to look into myself, but just in case you're faster: what if I just want to limit the number of pets in the "pets" array? :-) – Gabriel Aug 12 '16 at 14:26
  • Look at [**`$slice`**](https://docs.mongodb.com/manual/reference/operator/aggregation/slice/#exp._S_slice) operator and use it in `$project` stage instead of `$filter`. – mr.tarsa Aug 12 '16 at 14:37
  • Yes, I'm fighting with $slice, but I need some sorting before slicing... I want to slice the right ones :-) – Gabriel Aug 12 '16 at 14:48
  • To be clear, the actual query I'm looking for would be everything at once, but I decided to ask step by step and try each step by myself: - There's a condition over the age of the pet ($gte 1) - There's a max number of pets per user ($slice -3), the 3 oldest. - There are 3 types of pet and I have to split the pet array in 3 arrays by type, but that I'm sure I'll have to post-process :-) Thanks for the guidance :-) – Gabriel Aug 12 '16 at 14:56
  • Try to add one more `$project` stage with `$slice` operator after `$project` stage with `$filter` operator. – mr.tarsa Aug 12 '16 at 15:10
  • 1
    I still need to sort. The filter gives me the $gte 1 and the slice gives me a number of them, but no the oldest among them :-) I don't see how to pre-sort. – Gabriel Aug 12 '16 at 15:19
  • 2
    After `$filter` stage use [**`$unwind`**](https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/), [**`$sort`**](https://docs.mongodb.com/manual/reference/operator/aggregation/sort/), [**`$group`**](https://docs.mongodb.com/manual/reference/operator/aggregation/group/) like showed [here](http://stackoverflow.com/questions/15388127/mongodb-sort-inner-array), and then perform `$slice`. – mr.tarsa Aug 12 '16 at 15:48