CODEWITHSUNDEEP
mongodbaggregation-framework
P4TRYK
P4TRYK

Reputation: 169

mongoDB string in arrays conversion to double

I have a mongoDB containing entries like this:

{
        "_id" : ObjectId("5cf3e2e0839abf5afbf05052"),
        "sex" : "Male",
        "location" : {
            "city" : "Solingen",
            "address" : {
                "streetname" : "Dawn",
                "streetnumber" : "888"
            }
        },
        "credit" : [
            {
                "type" : "switch",
                "number" : "201864776633337",
                "currency" : "CNY",
                "balance" : "4898.89"
            },
            {
                "type" : "jcb",
                "number" : "3552382704063930",
                "currency" : "IDR",
                "balance" : "4501.62"
            }
        ]
    },
    {
        "_id" : ObjectId("5cf3e2e0839abf5afbf051c6"),
        "sex" : "Male",
        "location" : {
            "city" : "Hamburg",
            "address" : {
                "streetname" : "Brentwood",
                "streetnumber" : "6"
            }
        },
        "nationality" : "Germany",
        "credit" : [
            {
                "type" : "jcb",
                "number" : "4017959913393",
                "currency" : "SEK",
                "balance" : "3867.38"
            },
            {
                "type" : "jcb",
                "number" : "5100136044479699",
                "currency" : "CNY",
                "balance" : "4323.61"
            }
        ]
    },

I would like to perform using aggregate average and sum amount of money from cards.

So my code should map though the Arrays of cards and add balance on cards.

I tried to use map and mergeObjects

    [
        { "$addFields": {
            "credit": {
              "$map": {
                "input": "$credit",
                "in": {
                  "$mergeObjects": [
                    "$$this",
                    {
                      "convertedBalance": {
                        "$toDouble": "$$this.balance"
                      }
                    }
                  ]
                }
              }
            }
          }},
        { $unwind : "$credit" },
        { $match : { sex : "Female", nationality : "Poland"}},
        {$group : { _id : "$credit.currency", avgBalance : {$avg : "$convertedBalance"}, sumBalance : {$sum : "$convertedBalance"}}}
    ]
).toArray());

But the result is null for avgBalance and 0 for sumBalance like below:

connecting to: mongodb://localhost:27017/nbd?gssapiServiceName=mongodb
Implicit session: session { "id" : UUID("b49b33ce-808b-4814-a31e-7a70ce6fe0d7") }
MongoDB server version: 4.0.10
[
    {
        "_id" : "MYR",
        "avgBalance" : null,
        "sumBalance" : 0
    },
    {
        "_id" : "ALL",
        "avgBalance" : null,
        "sumBalance" : 0
    },

and the output should be something like:

        "_id" : "AFN",
        "avgBalance" : 5585.163333333333,
        "sumBalance" : 16755.489999999998
    },

Upvotes: 1

Views: 596

Answers (1)

mickl
mickl

Reputation: 49945

When you run $group stage convertedBalance is nested in credit while you're referencing to non-existing root level field, try:

{
    $group : { 
        _id : "$credit.currency", 
        avgBalance : {$avg : "$credit.convertedBalance"}, 
        sumBalance : {$sum : "$credit.convertedBalance"}
    }
}

Upvotes: 1

Related Questions