MongoDB filter documents based on items in other collections

Question

Say, I have 2 collections. Users and Orders as below: Users

{
 id: '01',
 name: 'john'
},
{
 id: '02',
 name: 'donald'
}

Orders

{
  id: '01',
  userId: '01'
},
{
  id: '02',
  userId: '02'
},
{
  id: '03',
  userId: '01'
}

I want to match all the Users that have more than 1 Order. I'm using localField, foreignField later in the pipeline. Example code being used:

db.Users.aggregate([
{
  $match: {
   activated: true
  }
},
{
 $sort: {
   Date: -1
 }
}
])

I want to filter some documents of Users collection based on the $lookup data. E.g $lookup: {from: 'Orders', localField: 'id', foreignField: 'userId', as: 'orders'}. how do I exclude documents from the aggregation that have fewer than 2 orders?

I want to filter some documents of "Users" collection based on the $lookup data.E.g $lookup: {from: 'Orders', localField: 'id', foreignField: 'userId', as: 'orders'}. how do I exclude documents from the aggregation that have fewer than 2 orders? — Shakil Ahmed
Please share collection and what you want data at json editor online ?? — Mahesh Bhatnagar

mickl mickl · Accepted Answer · 2019-12-22T10:06:14

You can add $match after $lookup stage and apply your filtering based on $size:

db.Users.aggregate([
    {
        $lookup: {from: 'Orders', localField: 'id', foreignField: 'userId', as: 'orders'}
    },
    {
        $match: { $expr: { $lt: [ { $size: "$orders" }, 2 ] } }
    }
])

Mongo Playground

MongoDB filter documents based on items in other collections

1 Answers