CODEWITHSUNDEEP
node.jsmongodbmongooseaggregation-framework
ChrisE
ChrisE

Reputation: 35

MongoDB: Dynamic Counts

I have two collections. A 'users' collection and an 'events' collection. There is a primary key on the events collection which indicates which user the event belongs to.

I would like to count how many events a user has matching a certain condition.

Currently, I am performing this like:


db.users.find({ usersMatchingACondition }).forEach(user => {

  const eventCount = db.events.find({
    title: 'An event title that I want to find',
    userId: user._id
  }).count();

  print(`This user has ${eventCount} events`);

});

Ideally what I would like returned is an array or object with the UserID and how many events that user has.

With 10,000 users - this is obviously producing 10,000 queries and I think it could be made a lot more efficient!

I presume this is easy with some kind of aggregate query - but I'm not familiar with the syntax and am struggling to wrap my head around it.

Any help would be greatly appreciated!

Upvotes: 1

Views: 495

Answers (2)

s_a94248
s_a94248

Reputation: 148

There isn't much optimization that can be done without aggregate but since you specifically said that

First, instead of

const eventCount = db.events.find({
    title: 'An event title that I want to find',
    userId: user._id
  }).count();

Do

const eventCount = db.events.count({
    title: 'An event title that I want to find',
    userId: user._id
  });

This will greatly speed up your queries because the find query actually fetches the documents first and then does the counting.

For returning an array you can just initialize an array at the start and push {userid: id, count: eventCount} objects to it.

Upvotes: 0

mickl
mickl

Reputation: 49945

You need $lookup to get the data from events matched by user_id. Then you can use $filter to apply your event-level condition and to get a count you can use $size operator

db.users.aggregate([
    {
        $match: { //users matching condition }
    },
    {
        $lookup:
        {
            from: 'events',
            localField: '_id', //your "primary key"
            foreignField: 'user_id',
            as: 'user_events'
        }
    },
    {
        $addFields: {
            user_events: {
                $filter: {
                    input: "$user_events",
                    cond: {
                        $eq: [
                            '$$this.title', 'An event title that I want to find'
                        ]
                    }
                }
            }
        }
    },
    {
        $project: {
            _id: 1,
            // other fields you want to retrieve: 1,
            totalEvents: { $size: "$user_events" }
        }
    }
])

Upvotes: 2

Related Questions