社区首页 >问答首页 >基于嵌套数组条件的mongoDB updateMany

问基于嵌套数组条件的mongoDB updateMany
EN

Stack Overflow用户

提问于 2020-02-10 03:59:27

回答 1查看 582关注 0票数 1

我在users集合中有以下结构：

[
  { "name": "Ivan", 
    "payments": [
      {"date": new Date("2019-01-01"), "details": [{"payment_system": "A", "spent": 95}, 
                                                   {"payment_system": "B", "spent": 123}]},
      {"date": new Date("2019-01-03"), "details": [{"payment_system": "A", "spent": 12}, 
                                                   {"payment_system": "B", "spent": 11}]}]},
  { "name": "Mark", 
    "payments": [
      {"date": new Date("2019-01-01"), "details": [{"payment_system": "D", "spent": 456}, 
                                                   {"payment_system": "B", "spent": 123}]}, 
      {"date": new Date("2019-01-02"), "details": [{"payment_system": "A", "spent": 98}, 
                                                   {"payment_system": "C", "spent": 4}]}]}
]

有没有办法在特定的支付系统中，在特定的日期范围内，向那些花费超过100美元的用户添加一个字段？我尝试过updateMany，但是不知道如何过滤基于payment_system字段的“细节”数组元素。

对于payment_system IN ("A", "C"), date >= "2019-01-02", spent_total >= 100，更新应该返回

[
  { "name": "Ivan", ...},
  { "name": "Mark", "filter_passed": true, ... }
]

mongodb

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-02-10 04:35:06

这个这个：

db.collection.aggregate([
   {
      $set: {
         payments: {
            $filter: {
               input: "$payments",
               cond: { $gte: ["$$this.date", new Date("2019-01-02")] }
            }
         }
      }
   },
   {
      $set: {
         spent_total: {
            $reduce: {
               input: "$payments.details.spent",
               initialValue: [],
               in: { $concatArrays: ["$$value", "$$this"] }
            }
         }
      }
   },
   { $set: { spent_total: { $sum: "$spent_total" } } },
   { $match: { "spent_total": { $gte: 100 } } }
])

Mongo 游乐场

更新：

payment_system的过滤器要长一点。你必须要$unwind和$group

db.collection.aggregate([
   {
      $set: {
         payments: {
            $filter: {
               input: "$payments",
               cond: { $gte: ["$$this.date", new Date("2019-01-02")] }
            }
         }
      }
   },
   { $unwind: "$payments" },
   {
      $set: {
         "payments.details": {
            $filter: {
               input: "$payments.details",
               cond: { $in: ["$$this.payment_system", ["A", "C"]] }
            },
         },
      }
   },
   {
      $group: {
         _id: { _id: "$_id", name: "$name", },
         payments: { $push: "$payments" }
      }
   },
   {
      $set: {
         spent_total: {
            $reduce: {
               input: "$payments.details.spent",
               initialValue: [],
               in: { $concatArrays: ["$$value", "$$this"] }
            }
         }
      }
   },
   { $set: { spent_total: { $sum: "$spent_total" } } },
   { $match: { "spent_total": { $gte: 100 } } },
   { // just some cosmetic
      $project: {
         _id: "$_id._id",
         name: "$_id.name",
         payments: 1
      }
   }
])

不能像db.collection.updateMany({}, [<the aggregation pipeline from above>])一样更新集合，因为它包含$unwind和$group。但是，您可以使$lookup或$out将整个结果保存到新集合中。

如果您需要对每个payment_system分别进行汇总，那么请尝试：

db.collection.aggregate([
   {
      $set: {
         payments: {
            $filter: {
               input: "$payments",
               cond: { $gte: ["$$this.date", new Date("2019-01-01")] }
            }
         }
      }
   },
   { $unwind: "$payments" },
   {
      $set: {
         "payments.details": {
            $filter: {
               input: "$payments.details",
               cond: { $in: ["$$this.payment_system", ["A", "B","C"]] }
            },
         },
      }
   },
   { $unwind: "$payments.details" },
   {
      $group: {
         _id: {
            _id: "$_id",
            name: "$name",
            payments: "$payments.details.payment_system"
         },
         spent_total: { $sum: "$payments.details.spent" }
      }
   },
   { $match: { "spent_total": { $gte: 100 } } },
   {
      $project: {
         _id: "$_id._id",
         name: "$_id.name",
         payments: "$_id.payments",
         spent_total: 1
      }
   }   
])