CODEWITHSUNDEEP
mongodb
Ilya
Ilya

Reputation: 13487

mongoDB updateMany based on nested array condition

I have following structure in users collection:

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

Is it any way to add a field to users who spent more than, lets say 100 during the specific date range in specific payment system? I tried updateMany, but have no idea how to filter "details" array element based on payment_system field.

For payment_system IN ("A", "C"), date >= "2019-01-02", spent_total >= 100 update should return

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

Upvotes: 1

Views: 776

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59466

This this one:

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 Playground

Update:

Filter by payment_system is a bit longer. You have to $unwind and $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
      }
   }
])

You cannot update your collection like db.collection.updateMany({}, [<the aggregation pipeline from above>]) because it contains $unwind and $group. However, you can make $lookup or $out to save entire result into new collection.

If you need to sum up for each payment_system individually then try:

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

Upvotes: 1

Related Questions