I have a collection where I store customers with their info from multiple other collections.
I need to organize a document where the yearly info is separated by year, starting from 2020 until the current year (so next year, it will still works).
Then, for each year, I need to perform some checking to get the info/status for each array item.
If the customer checks true for the specific yearly Item, I have to add it’s _id into a sub-array in the resulting query.
Here is a sample of my customers collection:
[
{
"_id": "308c9737-d216-485b-9172-372a554829b0",
"OfficeId": "b6dcfe76-faf6-44e4-a40f-4eff74378f67",
"Name": "Alicia Svetlana",
"Emails": [
{
"Address": "[email protected]",
"Title": "",
"IsMain": true,
"UseType": 0
}
],
"Status": 1,
"Statements": [],
"Budgets": [
{
"_id": {
"$binary": {
"base64": "pkr1dgTWTmSvY7BSAOC1lw==",
"subType": "04"
}
},
"CalendarYear": {
"_id": 2022
},
"BudgetStatus": 2,
"PaymentStatus": 3
}
],
"Backlogs": [
{
"CalendarYear": {
"_id": 2022
},
"ClosedAt": null
},
{
"CalendarYear": {
"_id": 2022
},
"ClosedAt": null
},
{
"CalendarYear": {
"_id": 2023
},
"ClosedAt": {
"$date": "2024-03-31T02:40:38.304Z"
}
},
{
"CalendarYear": {
"_id": 2023
},
"ClosedAt": null
}
],
"StatementDarfs": [],
"CashYearlyInformations": [
{
"CalendarYear": {
"_id": 2019
},
"CashBalance": 137512,
"HasXml": true,
"IsFinalized": false
},
{
"CalendarYear": {
"_id": 2020
},
"CashBalance": -2222,
"HasXml": true,
"IsFinalized": false
},
{
"CalendarYear": {
"_id": 2021
},
"CashBalance": -42682,
"HasXml": true,
"IsFinalized": false
},
{
"CalendarYear": {
"_id": 2022
},
"CashBalance": 0,
"HasXml": true,
"IsFinalized": false
},
{
"CalendarYear": {
"_id": 2023
},
"CashBalance": 13109,
"HasXml": true,
"IsFinalized": false
},
{
"CalendarYear": {
"_id": 2024
},
"CashBalance": -312845,
"HasXml": true,
"IsFinalized": false
}
],
"EcacStatus": []
},
{
"_id": "2ecdd519-960b-4ac8-be72-9dbd72ae055c",
"OfficeId": "b6dcfe76-faf6-44e4-a40f-4eff74378f67",
"CustomerGroupId": null,
"Name": "John Doe",
"Emails": [],
"Status": 1,
"Statements": [],
"Budgets": [],
"Backlogs": [
{
"CalendarYear": {
"_id": 2023
},
"ClosedAt": {
"$date": "2024-04-15T15:03:21.647Z"
}
}
],
"StatementDarfs": [],
"CashYearlyInformations": [
{
"CalendarYear": {
"_id": 2024
},
"CashBalance": 125772.6875,
"HasXml": true,
"IsFinalized": true
}
]
}
]
Some filters are not yearly, like Active, Inactive, WithEmail, WithoutEmail.
Others are yearly, like NegativeBalance(if CashBalance for that specific year is lower than zero, then I need to put the customer _id in the corresponding list if that item matches the condition.
Thing is, all yearly filter Specification must have all years in it’s Specifications arrays.
And the result I need is something like this:
[
{
"_id": "b6dcfe76-faf6-44e4-a40f-4eff74378f67",
"OfficeId": "b6dcfe76-faf6-44e4-a40f-4eff74378f67",
"FilterGroups": [
{
"Name": "GeneralFilterGroup",
"CalendarYearSupported": false,
"Filters": [
{
"Name": "ActiveCustomer",
"CalendarYear": {
"_id": 0
},
"Specifications": [
{
"CustomerIds" : [
"308c9737-d216-485b-9172-372a554829b0",
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
}
]
},
{
"Name": "InactiveCustomer",
"CalendarYear": {
"_id": 0
},
"Specifications": [
{
"CustomerIds" : [
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
}
]
},
{
"Name": "WithEmail",
"CalendarYear": {
"_id": 0
},
"Specifications": [
{
"CustomerIds" : [
"308c9737-d216-485b-9172-372a554829b0"
]
}
]
},
{
"Name": "WithoutEmail",
"CalendarYear": {
"_id": 0
},
"Specifications": [
{
"CustomerIds" : [
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
}
]
}
]
},
{
"Name": "NegativeBalanceFilterGroup",
"CalendarYearSupported": true,
"Filters": [
{
"Name": "NegativeBalance",
"Specifications": [
{
"CalendarYear": {
"_id": 2024
},
"CustomerIds" : [
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
},
{
"CalendarYear": {
"_id": 2022
},
"CustomerIds" : [
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
},
{
"CalendarYear": {
"_id": 2021
},
"CustomerIds" : [
"2ecdd519-960b-4ac8-be72-9dbd72ae055c"
]
},
{
"CalendarYear": {
"_id": 2020
},
"CustomerIds" : [
"308c9737-d216-485b-9172-372a554829b0
]
}
]
}
]
}
]
}
]