Mongodb查询:每个项目的日期最新记录 [英] Mongodb Query: Latest record by date for each item
问题描述
一个集合中有六个设备,每个设备都有很多记录,一些记录着新的日期,有些记录则有一个星期或一个月的历史.我需要一个查询,该查询返回每个设备的最新最近记录.对于.aggregate(),我需要提交完整的数据".
There are six devices in a collection, each has many records, some have records of new dates and some have week or/and month older. I need a query which returns latest last record of each device. In the case of .aggregate() I need that complete "data" filed.
这是示例json.
{
"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 no success.
在这种情况下,请注意以下事项.
Please not the following are not for this case.
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)
我正在寻找仅提供集合中每个设备的最新记录的查询.请注意,对于.aggregate(),我需要提交完整的数据".
I am looking for the query which provides only latest record of every device in collection. Please note that in the case of .aggregate() I need that complete "data" filed.
推荐答案
尝试以下代码段
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
}}
])
在上面的查询组中,按设备ID和日期,每一行中的数据和gateway_id将是最新的.
In above query group by device id and date, data, and gateway_id will be latest in each row.
输出为-
{
"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
}
谢谢
这篇关于Mongodb查询:每个项目的日期最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!