MongoDB aggregate query to filter documents created 7 days ago

user16463210
April 24, 2022
229 views
0 votes
2 Answers

I want to filter the dataset to extract documents which were created 7 days ago OR a Month ago OR Documents created at any date.

filter documents based on createdAt field in document.

Dataset:-

[
  {
    "_id": ObjectId("6257047cffd61ab62864c1ae"),
    "type": "A",
    "source": "B",
    "user": ObjectId("622b55ff0b0af6b049c387d3"),
    "createdAt": ISODate("2022-04-17T07:55:00.368Z"),
    "updatedAt": ISODate("2022-04-17T07:55:00.368Z"),
    
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1ad"),
    "type": "B",
    "source": "A",
    "user": ObjectId("622b55ff0b0af6b049c387d3"),
    "createdAt": ISODate("2022-04-23T07:55:00.368Z"),
    "updatedAt": ISODate("2022-04-23T07:55:00.368Z"),
    
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1ce"),
    "type": "A",
    "source": "C",
    "user": ObjectId("622b55ff0b0af6b049c387d3"),
    "createdAt": ISODate("2022-04-17T07:55:00.368Z"),
    "updatedAt": ISODate("2022-04-17T07:55:00.368Z"),
    
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1cb"),
    "type": "A",
    "source": "B",
    "user": ObjectId("622b56250b0af6b049c387d6"),
    "createdAt": ISODate("2022-04-24T07:55:00.368Z"),
    "updatedAt": ISODate("2022-04-24T07:55:00.368Z"),
    
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1cb"),
    "type": "A",
    "source": "B",
    "user": ObjectId("622b56250b0af6b049c387d6"),
    "createdAt": ISODate("2022-03-24T07:55:00.368Z"),
    "updatedAt": ISODate("2022-03-24T07:55:00.368Z"),
    
  },
  {
    "_id": ObjectId("6257047cffd61ab62864c1ce"),
    "type": "A",
    "source": "C",
    "user": ObjectId("622b55ff0b0af6b049c387d3"),
    "createdAt": ISODate("2022-03-17T07:55:00.368Z"),
    "updatedAt": ISODate("2022-03-17T07:55:00.368Z"),
    
  },
  
]

MongoDB aggregate query:-

db.collection.aggregate([
  {
    $addFields: {
      paramType: "All",
      paramSource: "All",
      paramCreatedAt:"All",
    }
  },
  {
    $match: {
      $and: [
        {
          user: ObjectId("622b55ff0b0af6b049c387d3")
        },
        {
          $or: [
            {
              paramType: {
                $eq: "All"
              }
            },
            {
              $expr: {
                $eq: [
                  "$paramType",
                  "$type"
                ],
                
              }
            }
          ]
        },
        {
          $or: [
            {
              paramSource: {
                $eq: "All"
              }
            },
            {
              $expr: {
                $eq: [
                  "$paramSource",
                  "$source"
                ]
              }
            }
          ]
        }
      ]
    }
  },
  {
    $setWindowFields: {
      output: {
        totalCount: {
          $count: {}
        }
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 6
  },
  {
    "$project": {
      "paramSource": false,
      "paramType": false,
      
    }
  }
])

how to filter to get documents created in the last 7 days or 30 days or any date.

paramCreatedAt will take one of the following values [All dates, 7 days ago, a month ago]

Example:-

If the All dates filter is applied then display all records.
If 7 days filter is applied display records created from the current date (which can be any day not necessary that it should be sunday) to 7 days back.
If 30 days filter applied then display records created in last 30 days

Answers

Your skeleton is pretty neat and you are actually quite close. For the date filtering, just use $dateDiff to return the date difference in days and compare it with the days interval your selected(i.e. 7 days or 30 days) by using $switch

db.collection.aggregate([
  {
    $addFields: {
      paramType: "All",
      paramSource: "All",
      paramCreatedAt: "All dates"// [All dates, 7 days ago, a month ago]
      
    }
  },
  {
    $match: {
      $and: [
        {
          user: ObjectId("622b55ff0b0af6b049c387d3")
        },
        {
          $or: [
            {
              paramType: {
                $eq: "All"
              }
            },
            {
              $expr: {
                $eq: [
                  "$paramType",
                  "$type"
                ],
                
              }
            }
          ]
        },
        {
          $or: [
            {
              paramSource: {
                $eq: "All"
              }
            },
            {
              $expr: {
                $eq: [
                  "$paramSource",
                  "$source"
                ]
              }
            }
          ]
        },
        {
          $or: [
            {
              paramCreatedAt: {
                $eq: "All dates"
              }
            },
            {
              $expr: {
                $and: [
                  {
                    "$in": [
                      "$paramCreatedAt",
                      [
                        "7 days ago",
                        "a month ago"
                      ]
                    ]
                  },
                  {
                    $lte: [
                      {
                        "$dateDiff": {
                          "startDate": "$createdAt",
                          "endDate": "$$NOW",
                          "unit": "day"
                        }
                      },
                      {
                        "$switch": {
                          "branches": [
                            {
                              "case": {
                                $eq: [
                                  "$paramCreatedAt",
                                  "7 days ago"
                                ]
                              },
                              "then": 7
                            },
                            {
                              "case": {
                                $eq: [
                                  "$paramCreatedAt",
                                  "a month ago"
                                ]
                              },
                              "then": 30
                            }
                          ]
                        }
                      }
                    ]
                  }
                ]
              }
            }
          ]
        }
      ]
    }
  },
  {
    $setWindowFields: {
      output: {
        totalCount: {
          $count: {}
        }
      }
    }
  },
  {
    $sort: {
      createdAt: -1
    }
  },
  {
    $skip: 0
  },
  {
    $limit: 6
  },
  {
    "$project": {
      "paramSource": false,
      "paramType": false,
      
    }
  }
])

Here is the Mongo playground for your reference.

Here’s an alternate approach using $facet. $facet is very handy because it allows you to "match and group in parallel" and create overlapping buckets of documents. A single pipeline with $group and $cond on the aggregation field works well for "if/then/elif/elif/else" constructions where overlaps are not desired and an order of precedence is desired.

db.foo.aggregate([
    // Initial filter(s):                                                                                                    
    {$match: {user: ObjectId("622b55ff0b0af6b049c387d3")}},

    // Create a single version of "now" from the perspective of the                                                          
    // CLIENT to use in queries to follow.                                                                                   
    // To create such a target date from the perspective of the SERVER,                                                      
    // use  {$addFields: {DD: '$$NOW'}}                                                                                      
    // Probably overkill but OK.                                                                                             
    {$addFields: {DD: new ISODate()}},

    {$facet: {
        "all": [ ],   // not exciting!  :-)                                                                                  

        "exactly_7_days_ago": [
            {$match: {$expr:
                      {$eq: [7, {$floor: {$divide:[{$subtract:['$DD', '$createdAt'] }, 1000 * 60 * 60 * 24]}} ]}
                     }}
        ],
        "everything_from_last_month": [
            {$match: {$expr:
                      {$eq: [1, {$subtract:[{$month: '$DD'}, {$month: '$createdAt'} ]} ]}
                     }}
        ],
        "only_one_day_from_last_month": [
            {$match: {$expr:
                      {$and: [
                          {$eq: [1, {$subtract:[{$month: '$DD'}, {$month: '$createdAt'}]} ]},
                          {$eq: [0, {$subtract:[{$dayOfMonth: '$DD'}, {$dayOfMonth: '$createdAt'} ]} ]}
                      ]}
                     }}
        ],
    }}

]);

Please signup or login to give your own answer.

Click here to cancel reply.