How to add calculated fields inside subdocuments without using unwind?

user_10669723 0
Viewed 61

I'm looking for a simple solution to add a field to a subdocument without using $unwind and $group. I need only to calculate the sum and the size of a nested subdocuments and show it in a new field. This is my starting collection:

{
  "_id": ObjectId("5a934e000102030405000000"),
  "subDoc": [
     {
       "a": 1,
       "subArray": [1,2,3]
     },
     {
       "b": 2
     }
   ]
},
{
  "_id": ObjectId("5a934e000102030405000001"),
  "subDoc": [
     {
       "a": 1,
       "subArray": [4,5,6]
     },
     {
       "b": 2
     },
     {
       "c": 3,
       "subArray": [8,8,8]
     }
  ]
}

And this is my desired result, where I've added sum (sum of subArray) and size (number of elements in subArray):

{
  "_id": ObjectId("5a934e000102030405000000"),
  "subDoc": [
     {
       "a": 1,
       "subArray": [1,2,3],
       "sum": 6,
       "size": 3
     },
     {
       "b": 2
       "sum": 0,
       "size": 0
     }
   ]
},
{
  "_id": ObjectId("5a934e000102030405000001"),
  "subDoc": [
     {
       "a": 1,
       "subArray": [4,5,6],
       "sum": 15,
       "size": 3
     },
     {
       "b": 2,
       "sum": 0,
       "size": 0
     },
     {
       "c": 3,
       "subArray": [8,8],
       "sum": 16,
       "size": 2
     }
  ] 
}

I know how to obtain this result using $unwind and then $group, but I'd like to know if there is any other way (or a better way!) to achieve the same result. I've tried using $addFields and $map without success.

Working playground example: https://mongoplayground.net/p/fK8t6SLlOHa

aggregation-framework mongodb
1 Answers
  • $map to iterate loop of subDoc array
  • $sum to get total of subArray array of numbers
  • $ifNull to check if field is not present or null then return empty array because $size operator only allows array input
  • $size to get total elements in subArray array
  • $mergeObjects to merge current object with new added fields
db.collection.aggregate([
  {
    $addFields: {
      subDoc: {
        $map: {
          input: "$subDoc",
          in: {
            $mergeObjects: [
              "$$this",
              {
                sum: { $sum: "$$this.subArray" },
                size: {
                  $size: { $ifNull: ["$$this.subArray", []] }
                }
              }
            ]
          }
        }
      }
    }
  }
])

Playground

user_89871280
Related