How get all elements which match condition in time period in array which contains only from date in Mongodb

user_1719569 0
Viewed 285

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.

event-sourcing aggregation-framework mongodb-query mongodb date
1 Answers

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'
                }
            }
        }
    } },
])
user_1220050
Related