Geek Answers Handbook

Mongodb Query: last record by date for each item

There are six devices in the collection, each has many entries, some have entries for new dates, and some have a week or a month. I need a query that returns the last last record of each device. In the case of .aggregate (), I need this complete "data".

Here is a json example.

{ "date_time" : some-date "device_id" : 27, "gateway_id" : 1, "data": [{"r" : 203,"v" : 3642},{"r" : 221,"v" : 3666}] } { "date_time" : some-date "device_id" : 28, "gateway_id" : 1, "data": [{"r" : 203,"v" : 3002},{"r" : 221,"v" : 3006}] } { "date_time" : some-date "device_id" : 29, "gateway_id" : 1, "data": [{"r" : 203,"v" : 3002}, {"r" : 221,"v" : 3006}] }

I tried a lot of queries but did not succeed.

Please do not relate to this.

 db.col.find({"device_id": {"$in": devices}, "date_time": { "$gte": last_date}}) .sort({$natural: -1}) db.col.find({"device_id": {"$in": devices}, "date_time": { "$gte": last_date}}) .sort({$natural: -1}).limit(1) db.col.find({"device_id": {"$in": devices}}) .sort({"date_time": -1}).limit(1) db.col.find({"device_id": {"$in": devices}}) .sort({"_id": -1}).limit(1)

I am looking for a query that provides only the most recent entry for each device in the collection. Note that in the case of .aggregate (), I need complete "data".

+3
source share
1 answer

Try the following snippet

 db.collection.aggregate([ {$group: { "_id": "$device_id", "gateway_id": {"$last":"$gateway_id"}, data: {$last: '$data'}, date: {$last: '$date_time'}, }}, {$project: { "device_id": "$_id", "gateway_id": "$gateway_id", "data": "$data", "date_time": "$date" }}, {$sort: { 'date': -1 }} ])

In the above group of requests by device ID and date, data and gateway_id will be the last in each row.

Exit -

 { "result" : [ { "_id" : 29, "gateway_id" : 1, "data" : [ { "r" : 203, "v" : 3002 }, { "r" : 221, "v" : 3006 } ], "device_id" : 29, "date_time" : "a" }, { "_id" : 28, "gateway_id" : 1, "data" : [ { "r" : 203, "v" : 3002 }, { "r" : 221, "v" : 3006 } ], "device_id" : 28, "date_time" : "b" }, { "_id" : 27, "gateway_id" : 1, "data" : [ { "r" : 203, "v" : 3642 }, { "r" : 221, "v" : 3666 } ], "device_id" : 27, "date_time" : "a" } ], "ok" : 1 }

thanks

+8
source

More articles:


All Articles