Фильтрования объектов в документах имеющих массивы на 3-х уровнях вложенности зависимо от значений в наименее и наиболее вложенном массиве mongoDB
Я буду благодарен за помощь по следующему вопросу. Вот коллекция в МонгоДБ (2 документа для демонстрационных целей):
{
"_id" : ObjectId("62684847e9594c65cbaa5d85"),
"agentId" : NumberInt(1),
"agentName" : "Yardi Gaondi",
"policyList" : [
{
"receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(1),
"policyStatusDate" : ISODate("2022-02-20T04:46:15.000+0000")
},
{
"policyDetailedCode" : NumberInt(2),
"policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
}
]
}
]
},
{
"receivedDate" : ISODate("2022-03-23T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(3),
"policyStatusDate" : ISODate("2022-01-16T04:46:15.000+0000")
}
]
}
]
},
{
"receivedDate" : ISODate("2022-02-23T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(1),
"policyStatusDate" : ISODate("2022-01-20T04:46:15.000+0000")
},
{
"policyDetailedCode" : NumberInt(2),
"policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
}
]
}
]
}
]
}
{
"_id" : ObjectId("62684847e9594c65cbaa5d86"),
"agentId" : NumberInt(2),
"agentName" : "Michelle Hazandi",
"policyList" : [
{
"receivedDate" : ISODate("2022-04-10T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(2),
"policyStatusDate" : ISODate("2022-04-09T05:46:15.000+0000")
}
]
}
]
},
{
"receivedDate" : ISODate("2022-03-10T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(2),
"policyStatusDate" : ISODate("2022-03-09T05:46:15.000+0000")
}
]
}
]
}
]
}
Итак коллекция состоит из 2-х документов, в каждом документе есть поле "policyList" которое является массивом объектов. В первом документе policyList содержит 3 объекта, во втором документе 2 объекта. Я должен отфильтровать документы в коллекции таким образом: 1) Нужно сохранить в массиве policyList только те объекты которые соответствуют такому условию: одно из полей объекта находиться в запрошенном временном промежутке, это означает что достаточно совпадения по крайней мере в одном из определённых по требованиям полей (первое поле - "receivedDate" которое находиться в массиве "policyList", тоесть на верхнем уровне вложенности, и второе поле "policyStatusDate" которое находиться в массиве "policiesArray", тоесть на самом глубоком третьем уровне вложенности, и если есть совпадение по одному из вышеуказанных полей мы возвращаем из "policyList" полный объект, тоесть мы не можем выбросить из массива "policiesArray" какой-нибудь объект). Одного совпадения достаточно, например если я хочу увидеть документы с 01/02/2022 до 01/03/2022 я ожидаю увидеть исходя из текущих данных коллекции в первом документе в массиве "policyList" только первый и третий объекты, поскольку первый объект попадает в запрошенный временный интервал по полю "policyStatusDate" - 20/02/2022 ( совпадения в одном из объектов массива "policiesArray" достаточно) и третий объект попадает в интервал по полю "receivedDate" - 23/02/2022), а второй объект я не ожидаю увидеть в результате запроса поскольку оба подлежащих проверке поля в нем не попадают в запрошенный временный промежуток с 01/02/2022 по 01/03/2022; 2) если нет совпадений среди объектов массива "policyList", тогда это будет значить что массив "policyList" будет пустой после фильтрации, и в таком случаю по требованиям мы не должны возвращать весь документ, вообщем в конечный результат запроса не попадают документы с пустым "policyList". Например, если я запрашиваю документы с 01/02/2022 по 01/03/2022 я не ожидаю что вернется второй документ, поскольку ни "policyStatusDate" ни "receivedDate" ни в одном из объектов массива "policyList" во втором документе коллекции не находятся в запрашиваемом временном промежутке.
Мой агрегационный запрос:
db.getCollection("offers2").aggregate([
{
$project: {
"agentId": "$agentId",
"agentName": "$agentName",
"policyList": {
$filter: {
input: "$policyList",
as: "item",
cond: {
"$or": [
{
"$and": [
{ "$gte": [ "$$item.receivedDate", ISODate("2022-02-01") ] },
{ "$lte": [ "$$item.receivedDate", ISODate("2022-03-01") ] }
]
},
{
$and": [
{ "$gte": [ "$$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-02-01") ] },
{ "$lte": [ "$$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-03-01") ] }
]
}
]
}
}
}
}
},
{
$project: {
"agentId": "$agentId",
"agentName": "$agentName",
"policyList": "$policyList",
"numPoliciesPerDate": {
$cond: {
if: {$isArray: "$policyList"}, then: {$size: "$policyList"}, else: "0"
}
}
}
},
{
$match: {
"numPoliciesPerDate": {$gte: 1}
}
}
])
После запуска этого запроса я ожидаю получить первый документ с первым и третьим объектом в массиве "policyList", но я получаю лишь третий объект (у которого есть совпадение по полю "receivedDate" ). Результат выполнения запроса.
{
"_id" : ObjectId("62684847e9594c65cbaa5d85"),
"agentId" : NumberInt(1),
"agentName" : "Yardi Gaondi",
"policyList" : [
{
"receivedDate" : ISODate("2022-02-23T04:46:15.000+0000"),
"policyStatusDetail" : [
{
"policyStsCode" : NumberInt(7),
"policiesArray" : [
{
"policyDetailedCode" : NumberInt(1),
"policyStatusDate" : ISODate("2022-01-20T04:46:15.000+0000")
},
{
"policyDetailedCode" : NumberInt(2),
"policyStatusDate" : ISODate("2022-01-19T05:46:15.000+0000")
}
]
}
]
}
],
"numPoliciesPerDate" : NumberInt(1)
}
Тоесть выходит что первый объект массива "policyList" первого документа не возвращается и видимо условие
$and": [
{ "$gte": [ "$$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-02-01") ] },
{ "$lte": [ "$$item.policyStatusDetail.policiesArray.policyStatusDate", ISODate("2022-03-01") ] }
]
не работает вообще. Вероятно нет возможности использовать точечную нотацию когда работаем с вложенными массивами. Помогите, пожалуйста, исправить данный агрегационный запрос (или посоветуйте как-написать новый если этот не подходит), чтобы он соответствовал требования описанных мною вначале, и в нашем случае чтобы первый объект массива "policyList" в первом документе коллекции также вернулся?