Question 316848 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Mongo aggregation with array of elements

Needs to do a data aggregation that summarizes the data in the root as well as in the sub-table

Having such input data

[
  {
    id: ID,
    points: 10,
    sources: [{
      sourceId: 's1',
      sourcePoints: 6,
    },
    {
      sourceId: 's2',
      sourcePoints: 4,
    }]
  }, 
  {
    id: ID,
    points: 2,
    sources: [{
      sourceId: 's1',
      sourcePoints: 2,
    }]
  }
];

I would like to get this summary

{
  id: ID,
  points: 12 // sum
  sources: {
    s1: { sourcePoints: 8 } // sum of all s1 from all records for sourceId = s1 and id = ID
    s2: { sourcePoints: 4 } // same as above, for sourceId = s2 and id = ID
    ... // sources can be N
  }
}

it is simple for my to get sum from root

[
 { $match: { id: ID } },
 { $group: { _id: $id, sum: { $sum: '$points' }} } 
]

but I have no idea how to add sum from arrays

Tags:

2

Answers


  1. 0

    For your issue you will probably need to work with the $arrayToObject operator since you can have N sources with unknown name.

    One solution could be as follow :

    db.collection.aggregate([
  {
    $match: {
      id: "ID"
    }
  },
  {
    "$unwind": "$sources"
  },
  {
    $group: {
      _id: {
        mainId: "$id",
        "sourceId": "$sources.sourceId"
      },
      sumPerSoureId: {
        $sum: "$sources.sourcePoints"
      }
    }
  },
  {
    $group: {
      _id: "$_id.mainId",
      totalPoints: {
        $sum: "$sumPerSoureId"
      },
      keyVal: {
        $push: {
          "k": "$_id.sourceId",
          "v": {
            "sourcePoints": "$sumPerSoureId"
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      id: "$_id",
      points: "$totalPoints",
      sources: {
        "$arrayToObject": "$keyVal"
      }
    }
  }
])

    You can try it for yourself on mongoplayground.

    The solution provided might not be optimized for millions of documents since i use two $unwind ate the beginning.

    Login or Signup to reply.
  2. 0

    Hope this help to you, I use $function to sum sourcePoint. https://mongoplayground.net/p/uPUQA8J4uz3

    [{
 $group: {
  _id: '$id',
  points: {
   $sum: '$points'
  },
  sources: {
   $push: '$sources'
  }
 }
}, {
 $addFields: 
{
  sources:{
    $function: {
      body: function (sources) {
        const result = {};
        for (let source of sources) {
          for (let detail of source) {
              if (result[detail.sourceId]) {
                  const current = result[detail.sourceId].sourcePoints + detail.sourcePoints;
                result[detail.sourceId] = {sourcePoints: current};
              } else {
                result[detail.sourceId] = {sourcePoints: detail.sourcePoints};
              }
          }
        }
        return result;
      },
      args: ['$sources'],
      lang: 'js'
    }
  }
}
}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search