mongoose aggregate on a nested array

Question

I have a schema that has a sub array and each of its elements reference another schema like so:

{
 _id: "5fed222d806311ec81d6df23"
 someOtherNestedDoc: {
    _id: "abc",
    someOtherField: 10
 },
 matches: [
   {
      _id: "5fed222d806311ec81d6daf3",
      user: "5fed222d806311ec81d6dcf3",
      probability: 10
   },
   {
      _id: "5fed222d806311ec81d6d1f3",
      user: "5fed222d806311ec81d62cf3",
      probability: 20
   },
 ]
}

I want to do a Mongoose aggregate, in which I lookup the user reference for every match, and only project 3 fields for it, 2 are already existing, the third one is the sum of an array of that schema, so that in the end, I have something like this:

{
 _id: "5fed222d806311ec81d6df23"
 someOtherNestedDoc: {
    _id: "abc",
    someOtherField: 10
 },
 matches: [
   {
      _id: "5fed222d806311ec81d6daf3",
      user: {
         firstName: "Max",
         lastName: "Mustermann",
         totalExpenses: 100
      },
      probability: 10
   },
   {
      _id: "5fed222d806311ec81d6d1f3",
      user: {
         firstName: "Herbert",
         lastName: "Mustermann",
         totalExpenses: 200
      },,
      probability: 20
   },
 ]
}

And the users would look like this:

{
  _id: "5fed222d806311ec81d6dcf3",
  firstName: "Max",
  lastName: "Mustermann",
  password: "test",
  expenses: [
    {
      _id: 1,
     price: 50
    },
    {
      _id: 2,
     price: 50
    },
   ]
}

{
  _id: "5fed222d806311ec81d62cf3",
  firstName: "Herbert",
  lastName: "Mustermann",
  password: "test2",
  expenses: [ 
    {
      _id: 3,
     price: 75
    },
    {
      _id: 4,
     price: 125
    },
   ]
}

turivishal · Accepted Answer · 2021-01-09 09:30:16Z

1

$unwind deconstruct matches array
$lookup with user collection and pass user id in let,
- $match userId condition
- $project to show required fields, get sum of expenses.price
$unwind deconstruct user array
$group by _id and reconstruct matches array

db.col1.aggregate([
  { $unwind: "$matches" },
  {
    "$lookup": {
      "from": "user",
      let: { userId: "$matches.user" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$userId"] } } },
        {
          $project: {
            _id: 0,
            firstName: 1,
            lastName: 1,
            totalExpenses: { $sum: "$expenses.price" }
          }
        }
      ],
      "as": "matches.user"
    }
  },
  { $unwind: "$matches.user" },
  {
    $group: {
      _id: "$_id",
      matches: { $push: "$matches" }
    }
  }
])

Playground

answered Jan 9, 2021 at 9:30

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

2 Comments

phoebus Over a year ago

that generally works well, the only thing is that, if I have other fields besides "matches" in the original document, then these fields are lost. How can I preserve those? I edited the question.

turivishal Over a year ago

you can use $first operator with field name look at this playground

Collectives™ on Stack Overflow

mongoose aggregate on a nested array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related