I have a collection with items like:
{
id: 1,
statusHistory: [
{
status: "ACTIVE",
date: ISODate("2020-04-26T22:02:26.000Z")
},
{
status: "DISABLED",
date: ISODate("2020-05-20T22:02:26.000Z")
}
]
}
{
id: 2,
statusHistory: [
{
status: "ACTIVE",
date: ISODate("2020-05-26T22:02:26.000Z")
}
]
}
{
id: 3,
statusHistory: [
{
status: "ACTIVE",
date: ISODate("2020-04-26T22:02:26.000Z")
},
{
status: "DISABLED",
date: ISODate("2020-04-27T22:02:26.000Z")
}
]
}
Now I need to find all items which had status ACTIVE in May 2020. The array statusHistory contains only dates when status was changed. I need somehow aggregate these array to a form where items contain to date too. Something like:
{
status: "ACTIVE",
date: ISODate("2020-04-26T22:02:26.000Z"), // from
dateTo: ISODate("2020-05-20T22:02:26.000Z") // it is from the date of the next item in the array
}
Then I would like to remove all items out of the period so I want this result:
{
id: 1,
statusHistory: [
{
status: "ACTIVE",
date: ISODate("2020-04-26T22:02:26.000Z")
}
]
}
{
id: 2,
statusHistory: [
{
status: "ACTIVE",
date: ISODate("2020-05-26T22:02:26.000Z")
}
]
}
I thought about to use somehow $reduce but I didn't find a solution. It look to me as a common problem in the event sourcing pattern but I am not able to find how to do it.
The following pipeline may not be the best but worth giving a try. Will update it with some explanations but to help with understanding the pipeline or to debug it should you get unexpected results, run the aggregation with just the first pipeline step. For example, run the aggregation in mongo shell as:
db.collection.aggregate([
{ '$addFields': { .... } }
])
Check the result to see if the new statusHistory array is constructed properly with a new field dateTo. If that gives the expected result, add the next:
db.collection.aggregate([
{ '$addFields': { .... } },
{ '$match': { ... } }
])
So overall, run the operation
db.collection.aggregate([
{ '$addFields': {
'statusHistory': {
'$map': {
'input': '$statusHistory',
'in': {
'$mergeObjects': [
'$$this',
{ 'dateTo': {
'$arrayElemAt': [
'$statusHistory',
{ '$indexOfArray': [
'$statusHistory.status',
'DISABLED'
] }
]
} }
]
}
}
}
} },
{ '$match': {
'$expr': {
'$gt': [
{ '$size': {
'$filter': {
'input': '$statusHistory',
'cond': {
'$and': [
{ '$eq': ['$$this.status', 'ACTIVE'] },
{ '$gte': ['$$this.dateTo.date', new Date('2020-05-01')] }
]
}
}
} },
0
]
}
} },
{ '$addFields': {
'statusHistory': {
'$map': {
'input': {
'$filter': {
'input': '$statusHistory',
'as': 'item',
'cond': { '$eq': ['$$item.status', 'ACTIVE'] }
}
},
'in': {
'status': '$$this.status',
'date': '$$this.date'
}
}
}
} },
])