I was supposed to filter documents based on month and year between query but I'm not getting 1 month of next year due to the condition i have used any other ways to filter out

Prathamesh_N · November 25, 2022, 7:23pm

{
	"_id" : ObjectId("63810333ae6cd2130104bfd7"),
	"calendarId" : ObjectId("63810333ae6cd2130104bfd6"),
	"date" : 26,
	"endDate" : "01-01-2024",
	"endMonth" : 1,
	"endTime" : "5:00 AM",
	"endYear" : 2024,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:02:27.432666Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2024-01-01",
	"reverseStartDate" : "2023-09-26",
	"sortDate" : 20230926,
	"sortTime" : 300,
	"startDate" : "26-09-2023",
	"startMonth" : 9,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:02:27.432696Z",
	"venue" : "MYSORE"
}
{
	"_id" : ObjectId("63810363ae6cd21301348ef9"),
	"calendarId" : ObjectId("63810df2ae6cd21301ad489b"),
	"date" : 28,
	"endDate" : "16-08-2022",
	"endMonth" : 8,
	"endTime" : "5:00 AM",
	"endYear" : 2022,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:48:18.809333Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2022-08-16",
	"reverseStartDate" : "2022-08-15",
	"sortDate" : 20220815,
	"sortTime" : 300,
	"startDate" : "15-08-2022",
	"startMonth" : 8,
	"startTime" : "3:00 AM",
	"startYear" : 2022,
	"title" : "New",
	"updatedAt" : "2022-11-25T18:48:18.809358Z",
	"venue" : "MYSORE"
}
{
	"_id" : ObjectId("638103baae6cd213014b43fd"),
	"calendarId" : ObjectId("638103baae6cd213014b43fc"),
	"date" : 30,
	"endDate" : "02-10-2023",
	"endMonth" : 10,
	"endTime" : "5:00 AM",
	"endYear" : 2023,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:04:42.263385Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2023-10-02",
	"reverseStartDate" : "2023-09-30",
	"sortDate" : 20230930,
	"sortTime" : 300,
	"startDate" : "30-09-2023",
	"startMonth" : 9,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:04:42.263411Z",
	"venue" : "MYSORE"
}
{
	"_id" : ObjectId("638103f9ae6cd2130198b544"),
	"calendarId" : ObjectId("638103f9ae6cd2130198b543"),
	"date" : 1,
	"endDate" : "02-10-2023",
	"endMonth" : 10,
	"endTime" : "5:00 AM",
	"endYear" : 2023,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:05:45.365134Z",
	"isActive" : false,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2023-10-02",
	"reverseStartDate" : "2023-10-01",
	"sortDate" : 20231001,
	"sortTime" : 300,
	"startDate" : "01-10-2023",
	"startMonth" : 10,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:52:55.336916Z",
	"venue" : "MYSORE"
}
{
	"_id" : ObjectId("63811207ae6cd21301772c14"),
	"calendarId" : ObjectId("63811207ae6cd21301772c13"),
	"endDate" : "02-09-2023",
	"endMonth" : 9,
	"endTime" : "5:00 AM",
	"endYear" : 2023,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T19:05:43.518031Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2023-09-02",
	"reverseStartDate" : "2022-10-01",
	"sortDate" : 20221001,
	"sortTime" : 300,
	"startDate" : "01-10-2022",
	"startMonth" : 10,
	"startTime" : "3:00 AM",
	"startYear" : 2022,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T19:05:43.518053Z",
	"venue" : "MYSORE"
}

query used to filter

 db.calendar_events_db.find({"$and":[{startMonth:{"$lte":9}},{endMonth:{"$gte":9}},{startYear:{"$lte":2023}},{endYear:{"$gte":2023}}]}).pretty()

expected documents

{
	"_id" : ObjectId("63810333ae6cd2130104bfd7"),
	"calendarId" : ObjectId("63810333ae6cd2130104bfd6"),
	"date" : 26,
	"endDate" : "01-01-2024",
	"endMonth" : 1,
	"endTime" : "5:00 AM",
	"endYear" : 2024,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:02:27.432666Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2024-01-01",
	"reverseStartDate" : "2023-09-26",
	"sortDate" : 20230926,
	"sortTime" : 300,
	"startDate" : "26-09-2023",
	"startMonth" : 9,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:02:27.432696Z",
	"venue" : "MYSORE"
}
{
	"_id" : ObjectId("638103baae6cd213014b43fd"),
	"calendarId" : ObjectId("638103baae6cd213014b43fc"),
	"date" : 30,
	"endDate" : "02-10-2023",
	"endMonth" : 10,
	"endTime" : "5:00 AM",
	"endYear" : 2023,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:04:42.263385Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2023-10-02",
	"reverseStartDate" : "2023-09-30",
	"sortDate" : 20230930,
	"sortTime" : 300,
	"startDate" : "30-09-2023",
	"startMonth" : 9,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:04:42.263411Z",
	"venue" : "MYSORE"
}

result

{
	"_id" : ObjectId("638103baae6cd213014b43fd"),
	"calendarId" : ObjectId("638103baae6cd213014b43fc"),
	"date" : 30,
	"endDate" : "02-10-2023",
	"endMonth" : 10,
	"endTime" : "5:00 AM",
	"endYear" : 2023,
	"groupId" : ObjectId("5f06cca74e51ba15f5167b86"),
	"insertedAt" : "2022-11-25T18:04:42.263385Z",
	"isActive" : true,
	"location" : {
		"latitude" : "127.386",
		"logitude" : "138.43"
	},
	"reminder" : "30 MIN",
	"reverseEndDate" : "2023-10-02",
	"reverseStartDate" : "2023-09-30",
	"sortDate" : 20230930,
	"sortTime" : 300,
	"startDate" : "30-09-2023",
	"startMonth" : 9,
	"startTime" : "3:00 AM",
	"startYear" : 2023,
	"title" : "New post test",
	"updatedAt" : "2022-11-25T18:04:42.263411Z",
	"venue" : "MYSORE"
}

Any way to filter the documents

Yilmaz_Durmaz · November 26, 2022, 2:27am

working with date format is not an easy job, especially when you try to calculate differences.

the code below uses $dateDiff to calculate the difference, but it needs proper dates. for that purpose, I used $dateFromParts. Also note that logic and comparison operators are also things to be used cautiously as they can wreak havoc if you do not notice what they include/exclude.

I also assumed you want a query for documents that have already started before (or starts at) 2023-09 and will end at least 1 month after that.

if you change the logic to “ends in that month” (change 1 to 0 in $gte) then you will also get the document with id “63811207ae6cd21301772c14” (“endDate”: “02-09-2023”).

use days to fine tune to difference, and do not forget the query depends on the assumptions above and may (and possibly will not) work if you change the requirements too much. Instead, use this as an example and try to understand how it is made.

db.collection.aggregate([
  {
    $addFields: {
      is_it_good: {
        $and: [
          {
            $lte: [
              {
                $dateDiff: {
                  startDate: {
                    $dateFromParts: {
                      year: 2023,
                      month: 9,
                    },
                  },
                  endDate: {
                    $dateFromParts: {
                      year: "$startYear",
                      month: "$startMonth",
                    },
                  },
                  unit: "day",
                },
              },
              0,
            ],
          },
          {
            $gte: [
              {
                $dateDiff: {
                  startDate: {
                    $dateFromParts: {
                      year: 2023,
                      month: 9,
                    },
                  },
                  endDate: {
                    $dateFromParts: {
                      year: "$endYear",
                      month: "$endMonth",
                    },
                  },
                  unit: "day",
                },
              },
              1,
            ],
          },
        ],
      },
    },
  },
  {
    $match: {
      is_it_good: true,
    },
  },
  {
    $unset: "is_it_good",
  },
]);

turivishal · November 26, 2022, 6:07am

As I can understand as per your query, you want to get the documents as per below conditions,

startDate: { $lte: "09-2023" },
// and
endDate: { $gte: "09-2023" }

You expected 2 documents in the result, but why is the below document will not come in the result?
Where startDate is “10-2022” and endDate is “09-2023”, it should come in the result as per your query.

{
    "_id": ObjectId("63811207ae6cd21301772c14"),
    "calendarId": ObjectId("63811207ae6cd21301772c13"),
    "endDate": "02-09-2023",
    "endMonth": 9,
    "endTime": "5:00 AM",
    "endYear": 2023,
    "groupId": ObjectId("5f06cca74e51ba15f5167b86"),
    "insertedAt": "2022-11-25T19:05:43.518031Z",
    "isActive": true,
    "location": {
      "latitude": "127.386",
      "logitude": "138.43"
    },
    "reminder": "30 MIN",
    "reverseEndDate": "2023-09-02",
    "reverseStartDate": "2022-10-01",
    "sortDate": 2.0221001e+07,
    "sortTime": 300,
    "startDate": "01-10-2022",
    "startMonth": 10,
    "startTime": "3:00 AM",
    "startYear": 2022,
    "title": "New post test",
    "updatedAt": "2022-11-25T19:05:43.518053Z",
    "venue": "MYSORE"
  }

You can use the below conditions if you want to match separately month and year,

two $or conditions, one for start date and one for end date
there are two possibilities, start year = 2023 and start month <= 9, OR start year < 2023
there are two possibilities, end year = 2023 and end month >= 9, OR end year > 2023

db.calendar_events_db.find({
  "$and": [
    {
      "$or": [
        {
          "startYear": 2023,
          "startMonth": { "$lte": 9 }
        },
        { "startYear": { "$lt": 2023 } }
      ]
    },
    {
      "$or": [
        {
          "endYear": 2023,
          "endMonth": { "$gte": 9 }
        },
        { "endYear": { "$gt": 2023 } }
      ]
    }
  ]
}).pretty()

Playground

system · December 1, 2022, 6:08am

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.