arraysmongodbsortingaggregation-framework

sorting in mongodb aggregation according to timestamp

i have this collection

{ "ip" : "192.168.141.1", "ifsIndex" : "28", "link" : "1", "timeStamp" : "19-01-2018 18:12:42" }
{ "ip" : "192.168.141.1", "ifsIndex" : "30", "link" : "1", "timeStamp" : "19-01-2018 18:12:42" }
{ "ip" : "192.168.141.1", "ifsIndex" : "32", "link" : "1", "timeStamp" : "19-01-2018 18:12:42" }
{ "ip" : "192.168.141.1", "ifsIndex" : "29", "link" : "1", "timeStamp" : "19-01-2018 18:12:42" }
{ "ip" : "192.168.141.1", "ifsIndex" : "31", "link" : "1", "timeStamp" : "19-01-2018 18:12:42" }
{ "ip" : "192.168.141.1", "ifsIndex" : "28", "link" : "1", "timeStamp" : "19-01-2018 18:14:16" }
{ "ip" : "192.168.141.1", "ifsIndex" : "29", "link" : "1", "timeStamp" : "19-01-2018 18:16:33" }
{ "ip" : "192.168.141.1", "ifsIndex" : "32", "link" : "1", "timeStamp" : "19-01-2018 18:14:09" }
{ "ip" : "192.168.141.1", "ifsIndex" : "28", "link" : "1", "timeStamp" : "19-01-2018 18:16:33" }
{ "ip" : "192.168.141.1", "ifsIndex" : "30", "link" : "1", "timeStamp" : "19-01-2018 18:14:16" }
{ "ip" : "192.168.141.1", "ifsIndex" : "31", "link" : "1", "timeStamp" : "19-01-2018 18:16:33" }
{ "ip" : "192.168.141.1", "ifsIndex" : "29", "link" : "1", "timeStamp" : "19-01-2018 18:14:16" }
{ "ip" : "192.168.141.1", "ifsIndex" : "30", "link" : "1", "timeStamp" : "19-01-2018 18:16:33" }
{ "ip" : "192.168.141.1", "ifsIndex" : "31", "link" : "1", "timeStamp" : "19-01-2018 18:14:16" }
{ "ip" : "192.168.141.1", "ifsIndex" : "32", "link" : "1", "timeStamp" : "19-01-2018 18:16:33" }

i want to group ip address and ifsIndex together and show the array of link and timeStamp and show the result in sorted timestamp.i tried this

db.events.aggregate([{$group:{_id:{"ifIndex":"$ifsIndex","ip":"$ip"},status:{$push:"$link"},timeStamp:{$push:"$timeStamp"}}},{$sort:{"timeStamp":1}}])

result i get is not i want. some values are not sorted in timeStamp

{ "_id" : { "ifIndex" : "31", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:16:33", "19-01-2018 18:14:16" ] }
{ "_id" : { "ifIndex" : "29", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:16:33", "19-01-2018 18:14:16" ] }
{ "_id" : { "ifIndex" : "32", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:09", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "30", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "28", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }

the timeStamp of ifIndex 31 and 29 are not sorted and others are sorted. what i am doing wrong here ?

Solution

  • $sort the timestamp and then do $group to get the $push sorted

    db.events.aggregate(
    [   
        {$sort:{"timeStamp":1}},
        {$group:{_id:{"ifIndex":"$ifsIndex","ip":"$ip"},status:{$push:"$link"},timeStamp:{$push:"$timeStamp"}}}
        
    ]
)

    result

    > db.events.aggregate( [ {$sort:{"timeStamp":1}}, {$group:{_id:{"ifIndex":"$ifsIndex","ip":"$ip"},status:{$push:"$link"},timeStamp:{$push:"$timeStamp"}}}  ])
{ "_id" : { "ifIndex" : "31", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "28", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "29", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "30", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:16", "19-01-2018 18:16:33" ] }
{ "_id" : { "ifIndex" : "32", "ip" : "192.168.141.1" }, "status" : [ "1", "1", "1" ], "timeStamp" : [ "19-01-2018 18:12:42", "19-01-2018 18:14:09", "19-01-2018 18:16:33" ] }
>

    There is a sort to sort the array element in update operations but not in aggregation doc