Query on Pymongo

user_17992606 0
Viewed 38

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

pymongo mongodb-query mongodb python
1 Answers

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

user_126417170
Related