如何访问mongodb聚合管道中的嵌套对象数组

Question

我有这样的文档(这是经过几个管道阶段后的结果(

[
{
"_id": ObjectId("5e9d5785e4c8343bb2b455cc"),
"name": "Jenny Adams",
"report": [
{ "category":"Beauty", "status":"submitted", "submitted_on": [{"_id": "xyz", "timestamp":"2022-02-23T06:10:05.832+00:00"}, {"_id": "abc", "timestamp":"2021-03-23T06:10:05.832+00:00"}] },
{ "category":"Kitchen", "status":"submitted", "submitted_on": [{"_id": "mnp", "timestamp":"2022-05-08T06:10:06.432+00:00"}] }
]
},
{
"_id": ObjectId("5e9d5785e4c8343bb2b455db"),
"name": "Mathew Smith",
"report": [
{ "category":"Household", "status":"submitted", "submitted_on": [{"_id": "123", "timestamp":"2022-02-23T06:10:05.832+00:00"}, {"_id": "345", "timestamp":"2021-03-23T06:10:05.832+00:00"}] },
{ "category":"Garden", "status":"submitted", "submitted_on": [{"_id": "567", "timestamp":"2022-05-08T06:10:06.432+00:00"}] },
{ "category":"BakingNeeds", "status":"submitted", "submitted_on": [{"_id": "891", "timestamp":"2022-05-08T06:10:06.432+00:00"}] }
]
}
]

我有一段时间的用户输入-

from - 2021-02-23T06:10:05.832+00:00
to - 2022-02-23T06:10:05.832+00:00

现在，我想从报告中筛选位于特定时间范围内的对象，我只想在"submitted_on[-1]["timestamp"]"位于(从和到(日期时间戳的范围内时保留该对象。由于嵌套，我很难访问时间戳我试过这个

$project: {
"name": 1,
"report": {
"category": 1,
"status": 1,
"submitted_on": 1,
"timestamp": {
$arrayElemAt: ["$report.cataloger_submitted_on", -1]
}
}
}

但是，这将获得报表数组{"_id": "bcd", "timestamp":"2022-05-08T06:10:06.432+00:00"}中报表内所有项目的最后一个对象。如何选择每个对象的最后时间戳。

Answer 1

您可以将聚合管道中的阶段替换为两个阶段：$unwind和$addFields，以获得我认为您想要的：

{
$unwind: "$report"
},
{
"$addFields": {
"timestamp": {
$arrayElemAt: [
"$report.submitted_on",
-1
]
}
}
},

$unwind阶段是将外部数组分解为文档，因为您希望对其中的每一个执行操作。以你的例子来看操场。如果您计划用更多步骤继续聚合管道，那么您可能可以跳过$addFields阶段，并在下一个$match阶段中包含该条件。