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' } }
  ]
});

There is no such feature as to use aggregation pipeline for delete. But, you can use a `$merger` or `$out` as the last stage of an aggregation pipeline where documents can be written to the same or different collection. It may not be efficient to use such operation for deleting just one document. — prasad_, Aug 22 '22 at 00:24

score 0 · Answer 1 · answered Aug 22 '22 at 06:33

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:

Create a TTL index on new field x
Use the aggregation pipeline, match the document you want then use last stage $merge to update that document to contain field x
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.
       }
    }
])

Aggregation for deleteOne(...) function in MongoDB

1 Answers1