CODEWITHSUNDEEP
mongodbmongoosemongodb-query
Joy Biswas
Joy Biswas

Reputation: 6527

lookup with 2 matching key one in foreign document another in same foreign sub document array mongo

I have 3 collections Plan, Group and Time

Plan {
  _id: ObjectId,
  group: ObjectId(ref=Group),
  time: ObjectId(ref=Time)
}

Group {
  _id: ObjectId,
  name: String
}

Time {
  _id: ObjectId,
  startDate: Date,
  exceptions: [{
    _id: ObjectId
    group: ObjectId(ref=Group)
    startDate: Date
  }]
}

I want to get all plans starting today where if today matches any 'startDate' field of field time from Plan collection to Time collection object

ie. Plan.time.startDate === today

or if matches any group and start date in exceptions

ie Plan.group === Plan.time.exceptions.group && Plan.time.exceptions.startDate === today

If the second condition group cannot be matched is also ok I can filter them out in script.

const dateRangeToday = [ '2021-01-23T00:00:00.000Z', '2021-01-23T23:59:59.999Z' ];
Plan.aggregate([
  { $match: { status: 'Planning' } },
  { $lookup: { from: 'time', localField: 'time', foreignField: '_id', as: 'time' } },
  { $unwind: '$time' },
  { $unwind: '$time.exceptions' },
  {
    $match: {
      $or: [
        { 'time.startDate': { $gte: dateRangeToday[0], $lte: dateRangeToday[1] } },
        {
          $and: [
            { 'time.exceptions.startDate': { $gte: dateRangeToday[0] } }  ,
            { 'time.exceptions.startDate': { $lte: dateRangeToday[1] } }  
            
          ]
        },
      ]
    }
  }
])

But always returning 0 documents. are my date queries fine for parent n subdocument. where am I wrong?

Upvotes: 0

Views: 459

Answers (1)

turivishal
turivishal

Reputation: 36114

It is expression match, try $expr if you want to match 2 internal fields,

{ $expr: { $eq: ["$group", "$time.exceptions.group"] } }

Playground

Question's second edit for ,

But always returning 0 documents.:

You have to convert your date type from string to date type:

const dateRangeToday = [ 
  new Date('2021-01-23T00:00:00.000Z'), 
  new Date('2021-01-23T23:59:59.999Z') 
];

Upvotes: 1

Related Questions