mongodbmongodb-querylaravel-mongodb

Mongodb filter data on multiple levels


I have a collection of employees containing employee data and some events with start and end times... I want to query data in order to get a subset of a specific employee's events based on the start and the end of events.

employees = [
  {
    _id: 5,
    events: [
      {
        start:5,
        end:10
      },
      {
        start:15,
        end:20
      },
      {
        start:21,
        end:26
      },
    ]
  },
  {
    _id: 6,
    events: [
      {
        start:3,
        end:10
      },
      {
        start:15,
        end:22
      },
      {
        start:23,
        end:26
      },
    ]
  }
]

The desired result: a list of events with start greater than x and end less than y for an employee with _id of z.

for now I am trying:

db.employees.aggregate([
  { $match: {'_id': 5}},
  { $unwind: '$events'}, 
  { $match: {'events.start': 2}
]);

but still I cant filter the subset using $gt/$lt.


Solution

  • This will work

    db.collection.aggregate([
      {
        $match: {
          "_id": 5,
        },
      },
      {
        $unwind: "$events"
      },
      {
        $match: {
          "events.start": {
            $gte: 15
          },
          "events.end": {
            $lte: 250
          }
        }
      }
    ])
    

    Working example for it. https://mongoplayground.net/p/smUNCxfzC_N