Home > Enterprise >  MongoDb : count documents in other collection without a join operation
MongoDb : count documents in other collection without a join operation

Time:12-21

I have 2 collections :

guilds:

{
  name: { type: String },
  owner: { type: String },
  discord: {
    id: { type: String }
  }
}

logCommands:

{
  discordId: { type: String },
  guild: { type: String },
  name: { type: String },
}

I have writed a function to get all guilds of a owner and count for each guild the sum of logs related to the guild.

The function:

Guild.aggregate([
    {
      $match: {
        owner: ObjectId(userId),
      },
    },
    {
      $lookup: {
        from: 'logcommands',
        localField: 'discord.id',
        foreignField: 'guild',
        as: 'logs',
      },
    },
    { $addFields: { commandCount: { $size: '$logs' } } },
  ]);

This is works but I don't want that mongoDB retrieves all logCommands concerned (for increase performance), I just want the count of logs for each guild, it is possible to get that without performs a join operation ($lookup) ?

Thanks for any help !

CodePudding user response:

Query

  • you can use lookup with pipeline, and the lookup result to have only the count number
  • the bellow needs MongoDB 5, with localfield and foreignfield to be the same with your lookup, except the small pipeline just to count
  • after lookup, $set is to change structure a bit.

Test code here

Guild.aggregate(
[{"$match":{"owner":1}},
 {"$lookup":
  {"from":"logcommands",
   "localField":"discord.id",
   "foreignField":"guild",
   "pipeline":[{"$count":"count"}],
   "as":"logs"}},
 {"$set":{"logs":{"$arrayElemAt":["$logs.count", 0]}}}])
  • Related