skip to Main Content

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

2

Answers


  1. 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.

    Login or Signup to reply.
  2. 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'} ]} ]}
                          ]}
                         }}
            ],
        }}
    
    ]);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search