How can i optimize this Mongo aggregation pipeline

user_19839236 0
Viewed 21

I am working on an application that shows a table of aggregate data I sum, part of the dashboard I want to show a sample of the last few events that came into the collection:

I am executing this pipeline but it crashes OOM because I have millions of documents in the collection, is there anything I can do? I want to get the last 5 texts

Events.aggregate([
    {
        $match: {event_type: {$in: [1,5,10,12]}}
    },
    {
        $group: {
            _id: "$event_type", avgTime: {$avg: "$time"}, avgCost: {$avg: "$cost"}, maxCost: {$max: "$cost"}, maxTime: {$max: "$time"}, texts: {$push: "$text"}
        }
    },
    {
        $addFields: {
            texts: { $slice: ["$text", {$subtract: [{$size: "$text"}, 5]}, {$size: "$text"}]}
        }
    }
])
mongoose aggregation-framework node.js mongodb
1 Answers

If you're using Mongo version 5.2+ then you can use the new $lastN operator, like so:

db.collection.aggregate([
  {
    $match: {
      event_type: {
        $in: [
          1,
          5,
          10,
          12
        ]
      }
    }
  },
  {
    $group: {
      _id: "$event_type",
      avgTime: {
        $avg: "$time"
      },
      avgCost: {
        $avg: "$cost"
      },
      maxCost: {
        $max: "$cost"
      },
      maxTime: {
        $max: "$time"
      },
      texts: {
        $lastN: {
          n: 5,
          input: "$text"
        }
      }
    }
  }
])

Mongo Playground

If you're on a lesser Mongo version, I recommend you just split this into 2 calls, and use a find instead that can utilize indexes:

const results = await db.collection.aggregate([
  {
    $match: {
      event_type: {
        $in: [
          1,
          5,
          10,
          12
        ]
      }
    }
  },
  {
    $group: {
      _id: "$event_type",
      avgTime: {
        $avg: "$time"
      },
      avgCost: {
        $avg: "$cost"
      },
      maxCost: {
        $max: "$cost"
      },
      maxTime: {
        $max: "$time"
      }
    }
  }
]);

// should use promise all or bluebird here instead, kept it a for loop for readability.
for (let i = 0; i < results.length; i++) {
    const lastTexts = await db.collection.find({event_type: results[i]._id}).sort({_id: -1}).limit(5);
    results[i].texts = lastTexts.map(v => v.text)
}
user_107703700
Related