mongoose - Conditional lookup with array of objects in mongodb

Question

I have two collections, one is products and the other is orders I want to write aggregation in products to have the matching orders.

Products

[{
 "id": "738097c4-5c52-11eb-ae93-0242ac130002",
 "title": "test product",
 "description": "Amet dolor justo erat sadipscing at sed sit et labore..",
 "combos": ["738097c4", "738097c5"]
},
{
 "id": "923097c4-5c52-11eb-ae93-0242ac1300cj2",
 "title": "test product 2",
 "description": "Acjhz cjzh ouhcio cho ",
 "combos": ["94563097c4", "84097e5"]
}]

Orders

[
{
  "id": "ce943752-7040-4926-9c1a-350633f4331f",
  "items": [
    {
      "itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
      "type": "product",
      "expiry": "2021-10-10"
    },
    {
      "itemId": "738097c4",
      "type": "combo",
      "expiry": "2021-12-10"
    }
  ]
},
{
  "id": "33c59dc4-c443-45a7-99c2-caba98f6d107",
  "items": [
    {
      "itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
      "type": "product",
      "expiry": "2022-11-10"
    },
    {
      "itemId": "738097c5",
      "type": "combo",
      "expiry": "2020-10-10"
    }
  ]
}
]

Expected Output

Products

[{
 "id": "738097c4-5c52-11eb-ae93-0242ac130002",
 "title": "test product",
 "description": "Amet dolor justo erat sadipscing at sed sit et labore..",
 "combos": ["738097c4", "738097c5"],
 "orders": [
       {
        "id": "ce943752-7040-4926-9c1a-350633f4331f",
        "items": [
                 {
                  "itemId": "738097c4-5c52-11eb-ae93-0242ac130002",
                  "type": "product",
                  "expiry": "2021-10-10"
                 },
                 {
                  "itemId": "738097c4",
                  "type": "combo",
                  "expiry": "2021-12-10"
                 }]
       }].

},
{
 "id": "923097c4-5c52-11eb-ae93-0242ac1300cj2",
 "title": "test product 2",
 "description": "Acjhz cjzh ouhcio cho ",
 "combos": ["94563097c4", "84097e5"],
 "orders:: []
}]

Matching Condition

Orders.items.expiry should be greater than current time

AND

(Any of the Orders.items.itemId should match products.id

OR

Orders.items.itemId should present inside products.combos)

Please help me to get the solution

question from:https://stackoverflow.com/questions/65838462/conditional-lookup-with-array-of-objects-in-mongodb

Answer 1

You can use $lookup to join collections

• $filter to filter out the matching date
• $lookup to join collection, I used uncorrelated-sub-queries

The script is

db.Order.aggregate(
    [{$addFields: {
      items: {
        $filter:{
          input:"$items",
          cond:{
            $gt:[{$toDate:"$$this.expiry"},new Date()]
          }
        }
      }
    }}, {$lookup: {
      from: 'Products',
      let:{itemIds:"$items.itemId"},
      pipeline:[
        {
          $match:{
            $expr:{
              $or:[
                  {$in:["$id","$$itemIds"]},
                  {$in:["$combos","$$itemIds"]}
                ]
            }
          }
        }
        ],
      as: 'join'
    }}]
)

Update 1

Since you need the output from products

[{$lookup: {
  from: 'Orders',
  let:{pId:"$id",comboArray:"$combos"},
  pipeline:[
    {$addFields: {
      items: {
        $filter:{
          input:"$items",
          cond:{
            $gt:[{$toDate:"$$this.expiry"},new Date()]
          }
        }
      }
    }},
   {
     $unwind:"$items"
   },
   {
      $match:{
        $expr:{
          $or:[
              {$eq:["$$pId","$items.itemId"]},
              {$in:["$items.itemId","$$comboArray"]}
            ]
        }
      }
    },
    {
      $replaceRoot:{
        newRoot:"$items"
      }
    }

       ],
  as: 'orders'
}}]

Working Mongo playground