mongodb中带有对象数组的条件查找

Question

我有两个集合，一个是 products ，另一个是 orders ，我想在产品中编写聚合以获得匹配的订单。

产品

[{
 "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"]
}]

命令

[
{
  "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"
    }
  ]
}
]

预期输出

产品

[{
 "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:: []
}]

匹配条件

Orders.items.expiry应该是比当前时间更大的

和

(任何一个Orders.items.itemId都应该与products.id匹配

或

Orders.items.itemId应该存在于products.combos)内部

请帮我找到解决办法

Answer 1

您可以使用$lookup连接集合。

• $filter筛选出匹配日期
• $lookup来加入集合，我使用了不相关子查询

剧本是

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'
    }}]
)

更新1

因为你需要产品的输出

[{$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 蒙戈游乐场