mongodbaggregation

In MongoDB query add count of matching documents from another collection


I have 2 collections:
Apartments:

{_id: '1', address: 'Street 1'},
{_id: '2', address: 'Street 2'},
{_id: '3', address: 'Street 3'},
{_id: '4', address: 'Street 4'}

And Users:

{_id: '1', name: 'User 1', favorites: ['1', '3']},
{_id: '2', name: 'User 2', favorites: ['2', '3', '4']},
{_id: '3', name: 'User 3', favorites: []}

I need to make a query that returns apartments details along with count of times it was added as favorite by users. So, something like:

{_id: '1', address: 'Street 1', favoritesCount: 1},
{_id: '2', address: 'Street 2', favoritesCount: 1},
{_id: '3', address: 'Street 3', favoritesCount: 2},
{_id: '4', address: 'Street 4', favoritesCount: 1}

So, in addition to default apartments details I need to have a field "favoritesCount" which finds count of includes in favorites arrays of users for each apartment.


Solution

  • Perhaps something like this:

    db.Apartments.aggregate([
    {
    "$lookup": {
      "from": "Users",
      "localField": "_id",
      "foreignField": "favorites",
      "as": "favoritesCount"
      }
     },
     {
     "$addFields": {
      "favoritesCount": {
        $size: "$favoritesCount"
       }
      }
     }
    ])
    

    Explanation:

    1. $lookup for existence for Apartments._id in Users.favorites
    2. replace favoritesCount with the result from count of the array via $size

    Playground