CODEWITHSUNDEEP
mongodbaggregation-framework
R.W
R.W

Reputation: 560

MongoDB Aggregation - Group by, Select additional fields in result

I have been trying to use MongoDB Aggregation operator for getting the last record (based on 'fixtm' which is the fixtime). Each record has a device id, fixtime and some other information like latitude, longitude, speed etc. My below aggregation works fine in a way that it returns me the last record for each device. But how do I get the other fields like latitude, longitude etc? I have tried adding things like lat: 1 in the $project list but no matter how many variations I have tried, the other values just don't show up. I think it's because they don't exist in the group operator's _id field or something? Any pointers will be greatly helpful. Thanks

function() {
    return db.gpspos.aggregate(
        [
            {
                $sort: {
                    devid: 1,
                    fixtm: 1
                }
            }, {
                $group: {
                    _id: "$devid",
                    lastData: {
                        $last: "$fixtm"
                    }
                }
            }, {
                $project: {
                    _id: 0,
                    device_id: '$_id',
                    fixtime: '$lastData'
                }
            }
        ]
    )
}

What I am getting from the above is

{ "device_id" : 5, "fixtime" : 1462368405000 }
{ "device_id" : 4, "fixtime" : 1462372097000 }

What I am looking at is this

{ "device_id" : 5, "fixtime" : 1462368405000, "speed": 70.3, "lat": 103.33434 }
{ "device_id" : 4, "fixtime" : 1462372097000, "speed": 70.3, "lat": 101.33434 }

An example of a document in my MongoDB

{
    "_id": {
        "$oid": "5729dc83f5be2411fb28c7c3"
    },
    "devid": 5,
    "devnm": "M.M (Samsung S3)",
    "fixtm": 1462360893000,
    "addr": "{address}",
    "lat": 1.433775,
    "lon": 103.7859717,
    "spdkm": 0
}

Upvotes: 2

Views: 3236

Answers (1)

chridam
chridam

Reputation: 103365

To return the other fields as well, you can use the same $last operator within your $group operation. This will return the corresponding fields from the last document for each group:

function() {
    return db.gpspos.aggregate(
        [
            {
                $sort: {
                    devid: 1,
                    fixtm: 1
                }
            }, {
                $group: {
                    _id: "$devid",
                    lastData: { $last: "$fixtm" },
                    speed: { $last: "$spdkm" },
                    address: { $last: "$address" },
                    lat: { $last: "$lat" },
                    lon: { $last: "$lon" }
                }
            }, {
                $project: {
                    _id: 0,
                    device_id: '$_id',
                    fixtime: '$lastData',
                    speed: 1,
                    address: 1,
                    lat: 1,
                    lon: 1
                }
            }
        ]
    )
}

Upvotes: 3

Related Questions