Filtering documents between older dates give increased results

I have a collection called student_quiz which has documents with this structure

{
	"_id" : ObjectId("66411abdb5c6372fe613c29d"),
	"admission_id" : 5122970,
	"subject_id" : 225,
	"unit_id" : 17375,
	"usm_id" : 15807,
	"acad_year" : 7,
	"school_subject_id" : 29131904,
	"shell_name" : "Unit",
	"shell_number" : "1",
	"subject_name" : "Biology",
	"unitno" : 3,
	"xp_version" : 3,
	"quiz" : [
		{
			"id" : 8853,
			"published_at" : ISODate("2024-05-13T01:08:37.145+05:30"),
			"complete_by" : ISODate("2024-05-18T01:08:37.145+05:30"),
			"total_questions" : "5",
			"correct" : 3,
			"wrong" : 2,
			"started_at" : ISODate("2024-05-13T01:08:37.503+05:30"),
			"completed_at" : ISODate("2024-05-13T02:24:36.677+05:30"),
			"updated_at" : ISODate("2024-05-13T02:24:36.656+05:30")
		},
		{
			"id" : 8855,
			"published_at" : ISODate("2024-05-13T01:12:17.860+05:30"),
			"complete_by" : ISODate("2024-05-18T01:12:17.860+05:30"),
			"total_questions" : "5",
			"correct" : 0,
			"wrong" : 0,
			"started_at" : ISODate("2024-05-13T01:12:18.168+05:30"),
			"completed_at" : null,
			"updated_at" : ISODate("2024-05-13T01:12:18.168+05:30")
		}
	]
},

I was running a query

db.student_quiz.find(
    {
        'quiz.updated_at': {
            $gt: ISODate("2024-06-22T18:30:00.000Z"), 
            $lt: ISODate("2024-06-23T18:30:00.000Z")  
        }
    }
)

Yesterday it was giving me 10 documents. I ran the same query today, it gave me 20. I am assuming that querying for older dates on different day should decrease the document. If i query it several times a day, sometimes it increases, sometimes it decreases. Sometimes, i even get documents where quiz array has elements with updated_at OUTSIDE of the date range.

Any insights?

Hello @Anurag_Sharma1,

Your condition will match any element from quiz array from a single document. For example, it might match the $gt condition with the first element and the $lt condition with another element.

You can use the $elemMatch operator to match both conditions for a single element in the quiz array. If any of the elements match then it will result document.

db.student_quiz.find(
    {
        quiz: {
            $elemMatch: {
                updated_at: {
                    $gt: ISODate("2024-06-22T18:30:00.000Z"), 
                    $lt: ISODate("2024-06-23T18:30:00.000Z")  
                }
            }
        }
    }
)
3 Likes