Referring my this question: Mongodb aggregate query to compare time in String format
I am facing below issues in output.
I have data saved in db : movies as below:
[
{
"timings": [
{
"start": "11:30 am",
"end": "03:00 pm"
},
{
"start": "11:30 pm",
"end": "03:00 pm"
}
]
},
{
"timings": [
{
"start": "02:30 am",
"end": "11:59 am"
},
{
"start": "11:30 pm",
"end": "03:00 pm"
}
]
},
{
"timings": [
{
"start": "08:30 am",
"end": "11:30 am"
},
{
"start": "09:30 am",
"end": "11:30 am"
}
]
},
{
"timings": [
{
"start": "00:30 am",
"end": "02:30 am"
}
]
},
{
"timings": []
}
]
I apply filters as follows in request query:
req.query = {starting: "03:00 am", ending: "11:59 pm"}
The aggregation query applied is as below:
db.collection.aggregate([
{"$unwind": "$timings"},
{$set: {
startHour: {$add: [
{$mod: [{$toInt: {$substr: ["$timings.start", 0, 2]}}, 12]},
{$divide: [{$toInt: {$substr: ["$timing.start", 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: ["$timings.start", 6, 2]}, "am"]}, 0, 12]}
]},
startingHour: {$add: [
{$mod: [{$toInt: {$substr: [req.query.starting, 0, 2]}}, 12]},
{$divide: [{$toInt: {$substr: [req.query.starting, 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: [req.query.starting, 6, 2]}, "am"]}, 0, 12]}
]},
endingHour: {$add: [
{$toInt: {$substr: [req.query.ending, 0, 2]}},
{$divide: [{$toInt: {$substr: [req.query.ending, 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: [req.query.ending, 6, 2]}, "am"]}, 0, 12]}
]},
endHour: {$add: [
{$toInt: {$substr: ["$timings.end", 0, 2]}},
{$divide: [{$toInt: {$substr: ["$timings.end", 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: ["$timings.end", 6, 2]}, "am"]}, 0, 12]}
]}
},
{$set: {
endHour: {$cond: [{$eq: ["$endHour", 0]}, 24, "$endHour"]},
endingHour: {$cond: [{$eq: ["$endingHour", 0]}, 24, "$endingHour"]}
}
},
{$match: {
$expr: {
$and: [{$gte: ["$startHour", "$startingHour"]},
{$lte: ["$endHour", "$endingHour"]}]
}
}
},
{$unset: ["startHour", "endHour", "startingHour", "endingHour"]}
])
Expected output should be like this: (count should be 3 as it satisfies the condition in aggregate. I should return the whole object that is stored in db, not the individual match condition
[
{
"timings": [
{
"start": "11:30 am",
"end": "03:00 pm"
},
{
"start": "11:30 pm",
"end": "03:00 pm"
}
]
},
{
"timings": [
{
"start": "02:30 am",
"end": "11:59 am"
},
{
"start": "11:30 pm",
"end": "03:00 pm"
}
]
},
{
"timings": [
{
"start": "08:30 am",
"end": "11:30 am"
},
{
"start": "09:30 am",
"end": "11:30 am"
}
]
}
]
But I am getting this as output: (this output is incorrect
[
{
"_id": ObjectId("5a934e000102030405000000"),
"timings": {
"end": "03:00 pm",
"start": "11:30 am"
}
},
{
"_id": ObjectId("5a934e000102030405000000"),
"timings": {
"end": "03:00 pm",
"start": "11:30 pm"
}
},
{
"_id": ObjectId("5a934e000102030405000001"),
"timings": {
"end": "03:00 pm",
"start": "11:30 pm"
}
},
{
"_id": ObjectId("5a934e000102030405000002"),
"timings": {
"end": "11:30 am",
"start": "08:30 am"
}
},
{
"_id": ObjectId("5a934e000102030405000002"),
"timings": {
"end": "11:30 am",
"start": "09:30 am"
}
}
]
I don't know where I am going wrong. Need help to understand my mistake and help me out in the query.
Mongodb playground is attached here : https://mongoplayground.net/p/LHEl-Xv6q53