Conver "distinct on" query from Sql in Mongodb with Pymongo

Question

我正在尝试从sql:转换此查询

select distinct on (id13) id13, timestamp1 
from oneindextwocolumnsfalse3years 
where timestamp1>='2010-01-01 00:00:00' and timestamp1<='2015-01-01 00:55:00' 
order by id13,timestamp1 desc

对mongodb那样：

mydb1.mongodbindextimestamp1.aggregate([
{
"$match": {
"timestamp1": {"$gte": datetime.strptime("2010-01-01 00:00:00", "%Y-%m-%d %H:%M:%S"),
"$lte" :datetime.strptime("2015-01-01 00:55:00", "%Y-%m-%d %H:%M:%S")}
}
},
{
"$group": {
"_id":{
"id_13":"$id13"
},
}
},
{
"$project": {
"_id": 0,
"id13":1,
"timestamp1":1
}
},
{"$sort": {"id13": 1,"timestamp1":-1}}
])

但它似乎不起作用。你有什么建议吗？我做错了什么，但我找不到什么！

Answer 1

试试这个管道：

db.collection.aggregate(
[
{
$match: {
timestamp: {
$gte: ISODate("2020-01-01"),
$lte: ISODate("2022-01-01")
}
}
},
{
$group: {
_id: "$id13", //define the grouping key
doc: { $first: "$$ROOT" } //pick the first doc from each group
}
},
{
$replaceWith: "$doc" //promote the doc to root
},
{
$project: {
_id: 0,
id13: "$id13",
timestamp: "$timestamp"
}
},
{
$sort: {
id13: 1,
timestamp: -1
}
}
]
)

https://mongoplayground.net/p/Cwzic2cMfgd