Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongo Aggregation remove all records for a id and only keeping the oldest one

have a collection with values

_id:ObjectId('......')
ton_id :ObjectId('abcd')
value:587900
date:2019-12-13T07:09:40.075+00:00


_id:ObjectId('......')
ton_id :ObjectId('abcd')
value:50540
date:2018-1-13T07:09:40.075+00:00

_id:ObjectId('......')
ton_id :ObjectId('abcd1')
value:55400
date:2019-5-13T07:09:40.075+00:00


_id:ObjectId('......')
ton_id :ObjectId('abcd1')
value:22500
date:2018-12-13T07:09:40.075+00:00


for the ton_ids abcd and abcd1 remove all the records except the oldest one.

required output

_id:ObjectId('......')
ton_id :ObjectId('abcd')
value:50540
date:2018-1-13T07:09:40.075+00:00



_id:ObjectId('......')
ton_id :ObjectId('abcd1')
value:22500
date:2018-12-13T07:09:40.075+00:00

like image 943
theUnknown Avatar asked Nov 07 '22 10:11

theUnknown


1 Answers

Something like should work. Use aggregation to pick the ids to keep followed by bulk updates to remove the other ids.

var bulk = db.getCollection(colname).initializeUnorderedBulkOp();

db.getCollection(colname).aggregate([
    {$match:{"ton_id":{"$in":[abcd, abcd1]}}},
    {$sort:{"date":1}},
    {$group:{
        "_id":"$ton_id", 
        "keep_id":{"$first":"$_id"}
    }},
    {$project:{"_id":0, "ton_id":"$_id", "keep_id":1}}
]).forEach(function(doc){ 
    bulk.find({"_id":{"$ne":doc.keep_id},"ton_id":doc.ton_id}).remove();
}); 
bulk.execute(); 
like image 134
s7vr Avatar answered Nov 15 '22 07:11

s7vr