mongoDB updateMany based on nested array condition

Question

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, ... }
]

So always in a specific date and specific payment system? could a payment system appear more than once in details of a date? — Tom Slabbaert
– Tom Slabbaert, Commented Feb 10, 2020 at 12:29
Do you want to update the documents or just want to retrieve? — SuleymanSah
– SuleymanSah, Commented Feb 10, 2020 at 13:09
@SuleymanSah update ideally, but as least retrieving would be good as well — Ilya
– Ilya, Commented Feb 10, 2020 at 13:10
Does spent total means the sum of the spent in the details array? — SuleymanSah
– SuleymanSah, Commented Feb 10, 2020 at 13:43

Wernfried Domscheit · Accepted Answer · 2020-02-10 14:07:16Z

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

thank you for answering! is there any way to filter payments.details by payment_system and then sum?
Bear in mind, this aggregation sum over all payment_system (i.e. A and C in this example)

Collectives™ on Stack Overflow

mongoDB updateMany based on nested array condition

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related