时间戳

首页 > 解决方案 > 在 mongodb 中使用对象数组进行条件查找

mongodb - 在 mongodb 中使用对象数组进行条件查找

问题描述

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

产品

[{
 "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中）

请帮助我获得解决方案

标签： mongodbmongoosemongodb-queryaggregation-framework

解决方案

你可以$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'
}}]

工作Mongo游乐场

推荐阅读