I need to fetch two field from a record in a collection, say "cpyKey" and "devices". multiple records are there having same "cpyKey" and "devices". I need to extract the record with latest "run_date" field available in record, and sort the outcome in ascending order of "devices"
Using pymongo. Can anyone help me in framing the query
agg_result=list(db.xyz.aggregate(
[#{
#"$match":{}},
{"$group":
{"_id":"$cpyKey","Max Devices":{"$max":"$devices"}}}, #{"$sum":1} -->Gives no. of records related to "_id":"$cpyKey" field
{"$sort":{"Max Devices":1}}
]))
previously I did this, but now , there needs to be a change .. instead of getting max of device, I need to have device from latest inserted record of particular cpyKey.
I tried :
mk=list(db.xyz.find({},{"_id":0,"run_date":1}).sort([('run_date',-1)]).limit(1))
print(mk)
#mk is extracting the latest run_date value, but it is associated with a cpyKey
agg_result=list(db.xyz.find({"run_date":mk[0]['run_date']},{"cpyKey":1,"devices":1,"_id":0}))
print(agg_result)
#while implimenting mk in above, it gives a record to which it is associated
But it does not give me values for all cpyKey
You can first sort by run_date
And then use $last in the group stage, to retrieve the corresponding Max Devices of the last element for each cpyKey
[
{
"$sort": {
"run_date": 1
}
},
{
"$group": {
"_id": "$cpyKey",
"Max Devices": {
"$last": "$Max Devices"
}
}
},
{
"$sort": {
"Max Devices": 1
}
}
]
try it here