Mongoose query to find documents where dates don't match within the same document

user_28068 0
Viewed 77

I am trying to write a mongoose query that can find all documents where REVIEW_DATE is within the last 24 hours and there is no action in the actions array that matches TYPE, DETAIL_TYPE, and ACTION_DATE does not match REVIEW_DATE. Basically if REVIEW_DATE changes, I need to re-perform the action, so the value of the review date is recorded in the actionDetails.

{
   reviewDate: <REVIEW_DATE>,
   actions: [
      {
         actionType: <TYPE>,
         actionDetails: {
            detailType: <DETAIL_TYPE>,
            valueOfReviewDateAtTimeOfAction: <ACTION_DATE>
         }
      }
   ]
}

Because mongo doesn't support a self join, I am pulling back all documents where REVIEW_DATE is within the last 24 hours and then iterating through the results filtering the list by iterating over the list of actions. Is there a way do do this with a single query or aggregation?

mongoose aggregation-framework mongodb-query mongodb
1 Answers

If I got your question correctly, I believe that you need to generate a query that uses the $expr operator as it allows you to use aggregate pipeline expressions within the same document such as

  • $dateDiff - get the difference between two dates and determine wether the <REVIEW_DATE> is within the last 24 hours using a system aggregation variable $$NOW as reference to the current datetime value
  • $filter - filter the actions array to return only those elements that match the given conditions
  • $size - returns the length of the filtered actions array
  • $gt/$lt - returns a bool that is used for the $expr condition to determine whether the filtered array has elements or not that satisfy the above query criteria on the same document.

For example, you could try running the following query:

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                { $dateDiff: { 
                    startDate: '$reviewDate', 
                    endDate: '$$NOW', 
                    unit: 'hour' 
                } }, 
                24 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$$this.actionType', 
                                    '$$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})

or for MongoDB versions which do not have support for $dateDiff

Model.find({ 
    $expr: {
        $and: [
            { $lt: [
                new Date(Date.now() - 1000 * 60 * 60 * 24), 
                '$reviewDate' 
            ] },
            { $gt: [
                { $size: { 
                    $filter: { 
                        input: '$actions',
                        cond: {
                            $and: [
                                { $ne: [
                                    '$reviewDate', 
                                    '$$this.actionDetails.valueOfReviewDateAtTimeOfAction'
                                ] },
                                { $ne: [
                                    '$$this.actionType', 
                                    '$$this.actionDetails.detailType'
                                ] },
                            ]
                        }
                    }
                } }, 
                0 
            ] }
        ]
    }
})
user_1220050
Related