在MongoDB中,我试图通过各自的组来过滤集合,只保留那些包含最新日期的文档。
在传统SQL中,我会执行以下操作:
Select *
From table a
Join (Select my_group, max(date) as max_date
From table group by my_group) b
ON a.my_group = b.my_group AND
a.date = b.max_date
用以下样品采集:
[
{
"_id": "123",
"item1": "group 1",
"item2": "abc",
"item3": "abc",
"date": "2022-01-01"
},
{
"_id": "234",
"item1": "group 1",
"item2": "abc",
"item3": "abc",
"date": "2022-01-02"
},
{
"_id": "345",
"item1": "group 1",
"item2": "abc",
"item3": "abc",
"date": "2022-01-02"
},
{
"_id": "789",
"item1": "group 2",
"item2": "abc",
"item3": "abc",
"date": "2022-01-01"
},
{
"_id": "678",
"item1": "group 2",
"item2": "abc",
"item3": "abc",
"date": "2022-01-02"
},
{
"_id": "456",
"item1": "group 2",
"item2": "abc",
"item3": "abc",
"date": "2022-01-02"
}
]
预期输出为:
[
{
"_id": "234",
"date": "2022-01-02",
"item1": "group 1",
"item2": "abc",
"item3": "abc"
},
{
"_id": "345",
"date": "2022-01-02",
"item1": "group 1",
"item2": "abc",
"item3": "abc"
},
{
"_id": "678",
"date": "2022-01-02",
"item1": "group 2",
"item2": "abc",
"item3": "abc"
},
{
"_id": "456",
"date": "2022-01-02",
"item1": "group 2",
"item2": "abc",
"item3": "abc"
}
]
我目前的最佳尝试是:
db.collection.aggregate([
{
$group: {
"_id": "$item1",
"max_date": {
$max: "$date"
},
"records": {
$push: "$$ROOT"
}
}
},
{
"$project": {
items: {
"$filter": {
"input": "$records",
"as": "records",
"cond": {
$eq: [
"$$records.date",
"$max_date"
]
}
}
}
}
},
{
$replaceRoot: {
newRoot: {
results: "$items"
}
}
}
])
不幸的是,这会返回按组划分的结果。我已经尝试了其他帖子建议的一些替代方法,得到了类似的问题,例如:
- How to group and select document corresponding to max within each group in MongoDB?
- MongoDB get rows where max value grouped
- Get all rows, groupped and with max value
下面是一个包含查询和示例数据的playground example。
1条答案
按热度按时间igetnqfo1#
你快找到答案了。
对于最后2个阶段:
$unwind
?将items
数组字段分解为多个文档.$replaceWith
?将输出文档替换为items
文档。Sample Mongo Playground
奖金
虽然上面的查询比较好,也想分享一下MongoDB查询,它类似于SQL实现。
$group
-按item1
分组并获取date
。$lookup
-使用item1
与date
自联接集合.并返回items
数组字段.$match
-过滤items
不是空数组的文档。$unwind
?将items
数组分解为多个文档.$replaceWith
?将输出文档替换为items
文档。Sample Mongo Playground (Bonus)