Add calculated field to documents in array field, using aggregation pipeline in MongoDB

Question

I`m trying to add a field representing relative weights to documents in an array. I have the following data model shown here with two documents:

db.pos.insertMany([
  {"asof": "2020-02-05",
  "holdings": [
    {"fid": 1,"shares": 50},
    {"fid": 2,"shares": 50}
  ],
  "portfolio_id": 10,
  "user": "xxx"
  },
  {"asof": "2020-02-02",
  "holdings": [
    {"fid": 1,"shares": 40},
    {"fid": 2,"shares": 60},
    {"fid": 3,"shares": 30}
  ],
  "portfolio_id": 10,
  "user": "xxx"
  }]);

Now, for each document in the array field "holdings" I`m trying to add "wt" which is simply shares/sum(shares), where sum is taken over all documents within holdings. My goal is to return the whole document with the "wt" addition, so the end result would look somethig like this:

{ "_id": ObjectId()
  "asof": "2020-02-05",
  "holdings": [
    {"fid": 1,"shares": 50, "wt": 0.5},
    {"fid": 2,"shares": 50, "wt": 0.5}
  ],
  "portfolio_id": 10,
  "user": "xxx"
  },

  {"_id": ObjectId()
  "asof": "2020-02-02",
  "holdings": [
    {"fid": 1,"shares": 40, "wt": 0.3076923},
    {"fid": 2,"shares": 60, "wt": 0.4615384},
    {"fid": 3,"shares": 30, "wt": 0.2307692}
  ],
  "portfolio_id": 10,
  "user": "xxx"
  }

I have tried routes/stages using $addField or $project together with $map ($divide and $sum) and some are closer to the desired result than others. But, I am nowhere near getting "inplace" results as shown above. Anyone able to help me out?

Dheemanth Bhat · Accepted Answer · 2021-03-24 11:35:40Z

1

Try this:

db.pos.aggregate([
    {
        $addFields: {
            "holdings": {
                $map: {
                    input: "$holdings",
                    as: "item",
                    in: {
                        fid: "$$item.fid",
                        shares: "$$item.shares",
                        wt: { $divide: ["$$item.shares", { $sum: "$holdings.shares" }] }
                    }
                }
            }
        }
    }
])

Output:

/* 1 createdAt:3/24/2021, 5:00:41 PM*/
{
    "_id" : ObjectId("605b22e149e1013fd8c32337"),
    "asof" : "2020-02-05",
    "holdings" : [
        {
            "fid" : 1,
            "shares" : 50,
            "wt" : 0.5
        },
        {
            "fid" : 2,
            "shares" : 50,
            "wt" : 0.5
        }
    ],
    "portfolio_id" : 10,
    "user" : "xxx"
},

/* 2 createdAt:3/24/2021, 5:00:41 PM*/
{
    "_id" : ObjectId("605b22e149e1013fd8c32338"),
    "asof" : "2020-02-02",
    "holdings" : [
        {
            "fid" : 1,
            "shares" : 40,
            "wt" : 0.3076923076923077
        },
        {
            "fid" : 2,
            "shares" : 60,
            "wt" : 0.46153846153846156
        },
        {
            "fid" : 3,
            "shares" : 30,
            "wt" : 0.23076923076923078
        }
    ],
    "portfolio_id" : 10,
    "user" : "xxx"
}

answered Mar 24, 2021 at 11:35

Dheemanth Bhat

4,5022 gold badges30 silver badges43 bronze badges

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

3 Comments

user3173412 Over a year ago

Perfect! I got a similar solution doing $mergeObject and the new field "wt", but looking at your solution, I see that it is unnecessary. Thank you sir!

Dheemanth Bhat Over a year ago

Nice you are using $mergeObjects i will check that out. Never used it that much.

user3173412 Over a year ago

Yes, got to this by banging my fist and head to the keyboard :) I guess the only difference is that I am merging fid and shares with a new "wt". Your solution is cleaner. For ref : in:{ $mergeObjects:[ "$$this", {wt:{ $divide:["$$this.shares", {$sum:"$holdings.shares"}] }} ] }

Collectives™ on Stack Overflow

Add calculated field to documents in array field, using aggregation pipeline in MongoDB

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related