get sum of integer from array of objects in mongodb

Question

I want to filter my documents by sum of decimal field in array of objects, but didn't find anything good enough. for example I have documents like below:

[
    {
        "id": 1,
        "limit": NumberDecimal("100000"),
        "requests": [
            {
                "money": NumberDecimal("50000"),
                "user": "user1"
            }
        ]
    },
    {
        "id": 2,
        "limit": NumberDecimal("100000"),
        "requests": [
            {
                "money": NumberDecimal("100000"),
                "user": "user2"
            }
        ]
    },
    {
        "id": 1,
        "limit": null,
        "requests": [
            {
                "money": NumberDecimal("50000"),
                "user": "user1"
            },
            {
                "money": NumberDecimal("50000"),
                "user": "user3"
            }
        ]
    },
]

description by documents fields:

limit - maximum amount of money, that I have
requests - array of objects, where money it's how much money user get from limit (if user1 get 50000 money there remainder it's 50000, limit - sum(requests.money))

I am making query in mongodb from scala projects:

get all documents where limit equal to null
get all documents where I have x remainder money (x like input value)

first case it's more easy than second one, I know how I can get sum of requests.money: I am doing it by this query:

db.campaign.aggregate([
    {$project: {
        total: {$sum: ["$requests.money"]}
    }}
])

scala filter part

Filters.or(
    Filters.equal("limit", null),
    Filters.expr(Document(s""" {$$project: {total: {$$sum: ["$$requests.money"]}}}"""))
)

But I don't want to store it and get as result, I want to filter by this condition x (money which I want to get by some user) limit >= sum(requests.money) + x. And by this filter I want to get all filtered documents.

Example: x = 50000 and output must be like this:

[
    {
        "id": 1,
        "limit": NumberDecimal("100000"),
        "requests": [
            {
                "money": NumberDecimal("50000"),
                "user": "user1"
            }
        ]
    },
    {
        "id": 1,
        "limit": null,
        "requests": [
            {
                "money": NumberDecimal("50000"),
                "user": "user1"
            },
            {
                "money": NumberDecimal("50000"),
                "user": "user3"
            }
        ]
    },
]

Wernfried Domscheit · Accepted Answer · 2020-09-22 07:01:59Z

2

You have to use an aggregation pipeline like this:

db.campaign.aggregate([
  {
    $set: {
      remainder: {
        $subtract: [ "$limit", { $sum: "$requests.money" } ]
      }
    }
  },
  {
    "$match": {
      $or: [
        { limit: null },
        { remainder: { $gte: 0 } }
      ]
    }
  },
  { $unset: "remainder" }
])

Mongo Playground

This one is also possible, but more difficult to read:

db.campaign.aggregate([
  {
    "$match": {
      $or: [
        { limit: null },
        {
          $expr: {
            $gt: [
              { $subtract: [ "$limit", { $sum: "$requests.money" } ] },
              0
            ]
          }
        }
      ]
    }
  }
])

edited Sep 22, 2020 at 7:01

answered Sep 22, 2020 at 6:56

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

get sum of integer from array of objects in mongodb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related