Mongod db nested inside nested element match and projection

Working with Data
1

my document structure is:

db={
  "booking": [
    {
      "_id": "5a934e000102030405000000",
      "cart": [
        {
          "_id": "5a934e000102030405000001",
          "productId": "5934e00010203040500031",
          "timeSlots": [
            {
              "_id": "5a934e00010203040500022",
              "spots": 10
            },
            {
              "_id": "5a934e00010203040540002",
              "spots": 2
            },
            {
              "_id": "5a934e00010203040505002",
              "spots": 1
            }
          ]
        },
        {
          "_id": "9934e00010203040500r33",
          "productId": "9934e00010203040500041",
          "timeSlots": [
            {
              "_id": "5a934e0001020304340002",
              "spots": 3
            },
            {
              "_id": "5a934e000102030405672",
              "spots": 8
            },
            
          ]
        }
      ]
    }
  ]
}

for query I have:

  1. bookingId
  2. productId
  3. cartId
  4. timeSlotId
  1. if only booking id matched then the query will return booking object, not other nested elements.
  2. if only bookingId and cartId matched then the query will return booking and cart object, timeSlotes will be ignored from nested document.
  3. if bookingId cartId and timeSlotId all three matched then the query will return booking, cart, and associated timeslot
2

Hi @Rafiq_Islam ,

I beilive the accurate feature for this is the $redact stage of aggregation starting with $match:

Having said that, I don’t like this syntax so much and it makes queries really complex.

I found the following solution:

[{$match: {
 _id: <BOOKING_ID>
}}, {$addFields: {
 cart: {
  $filter: {
   input: '$cart',
   as: 'obj',
   cond: {
    $ne: [
     '$$obj._id',
     <CART_ID>
    ]
   }
  }
 }
}}, {$addFields: {
 'cart.timeSlots': {
  $filter: {
   input: {
    $first: '$cart.timeSlots'
   },
   as: 'obj',
   cond: {
    $eq: [
     '$$obj._id',
     <SLOT_ID>
    ]
   }
  }
 }
}}]

In this example the matching and filtering using $filter will redact any unwanted values based on the parameters you provide to the stages.

Example based on your data, lets say we look for:
Booking: 5a934e000102030405000000
Cart : 9934e00010203040500r33
timeSlot: 5a934e00010203040540002

Of course you can play with the order and matching criteria as you like.

db.bookings.aggregate([{$match: {
 _id: '5a934e000102030405000000'
}}, {$addFields: {
 cart: {
  $filter: {
   input: '$cart',
   as: 'obj',
   cond: {
    $ne: [
     '$$obj._id',
     '9934e00010203040500r33'
    ]
   }
  }
 }
}}, {$addFields: {
 'cart.timeSlots': {
  $filter: {
   input: {
    $first: '$cart.timeSlots'
   },
   as: 'obj',
   cond: {
    $eq: [
     '$$obj._id',
     '5a934e00010203040540002'
    ]
   }
  }
 }
}}])
{ _id: '5a934e000102030405000000',
  cart: 
   [ { _id: '5a934e000102030405000001',
       productId: '5934e00010203040500031',
       timeSlots: [ { _id: '5a934e00010203040540002', spots: 2 } ] } ] }

thanks
Pavel

2 Likes