Querying array of objects with expression

[Thomas_Obermuller (Thomas Obermüller)]

I have a collection that I’d like to query by an array of objects. The query needs to calculate the difference between two dates and return only documents where the difference is less than or equal 7 days.

In following example, I’d like to have document 1, but not document 2.

{ 
  id: 1,
  entitlements:[{ 
    purchasedAt: 2022-07-20,
    expiresAt: 2022-07-27
  }] 
}, 
{ 
  id: 2,
  entitlements:[{ 
    purchasedAt: 2022-07-20,
    expiresAt: 2022-08-01
  }] 
}, 

I’m using Mongoose as ODM, but I’m failing to build a query or aggregation for the case above.

In detail I think I need to figure the following out:

1. How do I query a collection by objects of an array?
2. How do I include an $expr and $dateSubtract in the query?
3. How do I reference other attributes of the object that is currently looked at in the query?

Any help is appreciated. I’d also take a solution and figure the rest out myself from there

[Prasad_Saya]

Hello @Thomas_Obermuller, welcome to the MongoDB Community forum!

The following aggregation query :

return only documents where the difference is less than or equal 7 days.

var DAYS =  7 *24 * 60 * 60 * 1000    // 7 days in milliseconds

db.collection.aggregate([
{ 
  $addFields: { 
      entitlements: {
          $filter: { 
              input: '$entitlements', 
              cond: { $lte: [ 
                  { $subtract: [ { $toDate: "$$this.expiresAt" }, { $toDate: "$$this.purchasedAt" } ] }, 
                  DAYS
              ] }
          }
      }
  }
},
{ 
  $match: { entitlements: { $ne: [] } } 
}
])

I will try answer your questions here:

1. How do I query a collection by objects of an array?

It depends upon the data. In this case the date data is string type, the query requires that the difference be calculated and the data is in an array. So, the approach is to use the $filter aggregation array operator to filter on the condition. Note that the string date is converted to a Date object for the match operation. Then, check for documents in the following $match stage.

2. How do I include an $expr and $dateSubtract in the query?

$dateSubtract is used for subtracting units of time (e.g., days, mins, etc.) from a given date. So, this may not be useful in this case. Maybe you are thinking about $dateDiff. You can try using the $dateDiff in the above query.

3. How do I reference other attributes of the object that is currently looked at in the query?

I think you are referring to what is called as “projection”. With Aggregation queries, you can use $addFields (or its alias $set) and $project stages. They have different behavior, that, the $addFields includes all the fields and $project restricts the fields. In addition, the projections can include new fields (e.g., a calculated field value).

Please see the manual for the respective operators:

Aggregation Reference — MongoDB Manual

[Prasad_Saya]

You can also try the same query with a a single stage:

db.collection.aggregate([
{ 
    $match: {
        $expr: {
            $ne: [
              { $filter: { 
                  input: '$entitlements', 
                  cond: { $lte: [ 
                      { $subtract: [ { $toDate: "$$this.expiresAt" }, { $toDate: "$$this.purchasedAt" } ] }, 
                      DAYS
                  ] }
              } }, []
            ]
        }
    }
},
])

[Thomas_Obermuller (Thomas Obermüller)]

Thanks @Prasad_Saya, this really helped me. I could now create the aggregation.