In the lookup part in the aggregate method, how can I keep only documents that have a value in the foreign collection?

For instance, I have this collection users:

[
    { _id: 1, name: 'John', basketId: 4 },
    { _id: 2, name: 'mari', basketId: 9 },
    { _id: 3, name: 'tedd', basketId: 32 },
    { _id: 4, name: 'sara', basketId: 14 },
    { _id: 5, name: 'jane', basketId: 3 },
    .
    .
    .
]

And another collection named baskets

[
    { _id: 1, items: 0 },
    { _id: 2, items: 2 },
    { _id: 3, items: 0 },
    { _id: 4, items: 0 },
    { _id: 5, items: 7 },
    .
    .
    .
]

Now if I want to get users with basket items greater than 0, I use aggregate and lookup:

UserModel.aggregate([
  { $lookup:
      {
        from: 'baskets',
        localField: 'basketId',
        foreignField: '_id',
        pipeline: [{ $match: { items: { $gt: 0 } } }],
        as: 'basket'
      }
  }
])

It brings up ALL users with their basket data. For those users whose basket items are 0, it shows basket: [].

But I need to get ONLY users that have basket items greater than 0. How can it be done?

CodePudding user response：

You shouldn't place the $match stage in the pipeline of $lookup. As what it did is filter the documents to be returned in the basket array.

Instead, you need a $match stage to filter the documents by comparing the first document's items value in the basket array.

UserModel.aggregate([
  {
    $lookup: {
      from: "baskets",
      localField: "basketId",
      foreignField: "_id",
      as: "basket"
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          {
            $first: "$basket.items"
          },
          0
        ]
      }
    }
  }
])

Demo 1 @ Mongo Playground

The question is ambiguous. You may look for the below query as well (but would return the same result as Demo 1):

UserModel.aggregate([
  {
    $lookup: {
      from: "baskets",
      localField: "basketId",
      foreignField: "_id",
      pipeline: [
        {
          $match: {
            items: {
              $gt: 0
            }
          }
        }
      ],
      as: "basket"
    }
  },
  {
    $match: {
      $expr: {
        $gt: [
          {
            $size: "$basket"
          },
          0
        ]
      }
    }
  }
])

Or check is not an empty array

{
  $ne: [
    "$basket",
    []
  ]
}

Demo 2 @ Mongo Playground