Mongodb聚合通过字符串日期获取文档

Question

我有类似的文档列表

"Location_id": "60b53d92ccb1483964da45f9",
"SensorIdentifier": "CCCCCCCCCCCCCCCC",
"SensorType": "Temperature",
"status": true,
"data": [
{
"temperature": 22.5,
"humidite": 38.2,
"batterie": 50,
"time": 1623083627736,
"date": "7-6-2021"
},

我想按日期过滤它们，例如，我想通过过滤数据数组中的子文档来获得日期在6-6-2021和7-6-2021之间的文档，注意，日期字段是字符串，现在是我的解决方案：

{
"$match": {
"$expr": {
"$and": [
{
"$gte": [
"$date",
{
"$dateFromString": {
"dateString": "4-6-2021",
"format": "%m-%d-%Y"
}
}
]
},
{
"$lte": [
"$date",
{
"$dateFromString": {
"dateString": "7-6-2021",
"format": "%m-%d-%Y"
}
}
]
}
]
}
}
},

Answer 1

你可以这样做：

db.collection.aggregate([
{
"$match": {
"Location_id": "60b53d92ccb1483964da45f9"
}
},
{
"$set": {
"data": {
"$filter": {
"input": "$data",
"cond": {
"$and": [
{
"$lte": [
{
"$dateFromString": {
dateString: "$$this.date",
format: "%m-%d-%Y"
}
},
{
"$dateFromString": {
dateString: "7-6-2021",
format: "%m-%d-%Y"
}
}
]
},
{
"$gte": [
{
"$dateFromString": {
dateString: "$$this.date",
format: "%m-%d-%Y"
}
},
{
"$dateFromString": {
dateString: "4-6-2021",
format: "%m-%d-%Y"
}
}
]
}
]
}
}
}
}
}
])

以下是工作示例：https://mongoplayground.net/p/-l2O6wp9SFV