Let's say given following data:

[
{
    _id: 61708f71ee435de97b5a82e7,
    title: 'mjGPzpGLKIdea Title',
    author: 61708f6dee435de97b5a82e2
    votes: [
      6171c8437234d56cfeff9986,
      6171c8647234d56cfeff99de
    ]
  },
  {
    _id: 6171c522069a2f6b396a430a,
    title: 'MnJYCZFtyIdea Title',
    author: 61708f7cee435de97b5a82f2,
    votes: [ 6171c8927234d56cfeff9a45 ],
  },
  {
    _id: 6171c523069a2f6b396a4315,
    title: 'pcOKJzOEAIdea Title',
    author: 61708f7cee435de97b5a82f2,
    votes: [
      6171c8327234d56cfeff9950,
      6171c83c7234d56cfeff9967,
      6171c85d7234d56cfeff99bb,
      6171c8817234d56cfeff9a11
    ]
  }
]

Each vote in votes has structure like this:

{
    _id: 6171c8817234d56cfeff9a11, // some id
    score: -1, // -1 or  1
}

How to get same array but with reduced score in each item using Mongoose:

[
{
    _id: 61708f71ee435de97b5a82e7,
    title: 'mjGPzpGLKIdea Title',
    author: 61708f6dee435de97b5a82e2
    scoreSum: 0 // -1   1
  },
  {
    _id: 6171c522069a2f6b396a430a,
    title: 'MnJYCZFtyIdea Title',
    author: 61708f7cee435de97b5a82f2,
    scoreSum: -1,
  },
  {
    _id: 6171c523069a2f6b396a4315,
    title: 'pcOKJzOEAIdea Title',
    author: 61708f7cee435de97b5a82f2,
    scoreSum: 3 // 1   1   1 - 1
  }
]

I tried to use $reduce aggregation with $add, but $$this.score returns me null in the end. Like this:

MyModel.aggregate([
    {
      $addFields: {
        scoreSum: {
          $reduce: {
            input: '$votes',
            initialValue: 0,
            in: { $add: ['$$value', '$$this.score'] },
          },
        },
      },
    },
  ])

I don't want to use javascript and I want to use only request to database and reduce value in elements. Thank you.

CodePudding user response：

I dont know what are you doing wrong, but you need a lookup also.

Query

• lookup to join array with single field
  (join happens if votes array contains that _id)
• reduce the and sume the scores
• "$$REMOVE" is a system variable to remove the field votes, you can avoid it and project or unset the field votes

Test code here

authors.aggregate(
[{"$lookup": 
    {"from": "votes",
      "localField": "votes",
      "foreignField": "_id",
      "as": "scoreSum"}},
  {"$set": 
    {"scoreSum": 
      {"$reduce": 
        {"input": "$scoreSum",
          "initialValue": 0,
          "in": {"$add": ["$$value", "$$this.score"]}}},
     "votes": "$$REMOVE"}}])

There is alternative solution with $lookup with pipeline, and instead of reduce, group by null and sum, but this looks more like your solution.