Aggregation for deleteOne(...) function in MongoDB

Question

I'm trying to delete a document with one operation, instead of two - countDocuments(...), deleteOne(...).

Condition: delete oldest document with user_id = xxx, if count of documents with user_id >= 5.

It's works, but it lacks document count check. I still can’t figure out how to form an aggregation pipeline for deleteOne(...), of course if possible it.

db.getCollection("collection_name").deleteOne({
  $and: [
    { user_id: { $eq: '118d43cc-3f03-45a1-94f5-03a053d0b78b' } },
    ... ???,
    { $expr: { $min: '$created_at' } }
  ]
});

Answer 1

You can't really merge a deleteOne operations with an aggregation, but for this specific usecase there is a workaround.

You could define a TTL index on a "new" field, then in your aggregation use $merge to "update" the document you want to delete by adding this TTL field.

So to summarise:

1. Create a TTL index on new field x
2. Use the aggregation pipeline, match the document you want then use last stage $merge to update that document to contain field x
3. within 60 seconds MongoDB will clear this object which is the only caveat, if you must ensure immediate cleansing then this workaround is now valid - however you can "Replace" it with an empty document.

This would look similar to this:

// create index
db.collection.createIndex({ x: 1 }, { expireAfterSeconds: 1, sparse: true })


// pipeline
db.collection.aggregate([
    {
        $match: {
            user_id: '118d43cc-3f03-45a1-94f5-03a053d0b78b'
        }
    },
    {
        $group: {
            _id: null,
            count:{ $sum: 1 },
            minId: {$min: "$_id"}
        }
    },
    {
        $match: {
            count: {$gt: 5}
        }
    },
    {
        $project: {
            _id: "$minId",
            x: "$$CURRENT"
        }
    },
    {
       $merge :{
           into: "collection",
           on: "_id",
           whenMatched: "replace", // replace will esnure the document is not matched by other queries until it's cleared. you can use "merge" into
           whenNotMatched: "discard" // this should not happen, only if you execute multiple piplines at once.
       }
    }
])