CODEWITHSUNDEEP
javascriptmongodbmongooseaggregation-framework
slider
slider

Reputation: 2816

Mongo find by sum of subdoc array

I'm trying to find stocks in the Stock collection where the sum of all owners' shares is less than 100. Here is my schema.

const stockSchema = new mongoose.Schema({
  owners: [
    {
      owner: {
          type: Schema.Types.ObjectId,
          ref: "Owner"
      },
      shares: {
          type: Number,
          min: 0,
          max: 100
      }
    }
  ]
}

const Stock = mongoose.model("Stock", stockSchema);

I've tried to use aggregate but it returns a single object computed over all stocks in the collection, as opposed to multiple objects with the sum of each stock's shares.

stockSchema.statics.getUnderfundedStocks = async () => {
  const result = await Stock.aggregate([
    { $unwind: "$owners" },
    { $group: { _id: null, shares: { $sum: "$owners.shares" } } },
    { $match: { shares: { $lt: 100 } } }
  ]);
  return result;
};

So, rather than getting:

[ { _id: null, shares: 150 } ] from getUnderfundedStocks, I'm looking to get:

[ { _id: null, shares: 90 }, { _id: null, shares: 60 } ].

I've come across $expr, which looks useful, but documentation is scarce and not sure if that's the appropriate path to take.

Edit: Some document examples:

/* 1 */
{
    "_id" : ObjectId("5ea699fb201db57b8e4e2e8a"),
    "owners" : [ 
        {
            "owner" : ObjectId("5ea62a94ccb1b974d40a2c72"),
            "shares" : 85
        }
    ]
}

/* 2 */
{
    "_id" : ObjectId("5ea699fb201db57b8e4e2e1e"),
    "owners" : [ 
        {
            "owner" : ObjectId("5ea62a94ccb1b974d40a2c72"),
            "shares" : 20
        }, 
        {
            "owner" : ObjectId("5ea62a94ccb1b974d40a2c73"),
            "shares" : 50
        }, 
        {
            "owner" : ObjectId("5ea62a94ccb1b974d40a2c74"),
            "shares" : 30
        }
    ]
}

I'd like to return an array that just includes document #1.

Upvotes: 1

Views: 46

Answers (1)

Ashh
Ashh

Reputation: 46441

You do not need to use $group here. Simply use $project with $sum operator.

db.collection.aggregate([
  { "$project": {
    "shares": { "$sum": "$owners.shares" }   
  }},
  { "$match": { "shares": { "$lt": 100 } } }
])

Or even you do not need to use aggregation here

db.collection.find({
  "$expr": { "$lt": [{ "$sum": "$owners.shares" }, 100] }
})

MongoPlayground

Upvotes: 2

Related Questions