How to return unique array of objects using $set in mongodb aggregation

Viewed 35

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

0 Answers
Related