MongoDB slow $unwind and $group aggregation

Question

im having some trouble with my MongoDB aggregation. I have documents like:

{
    _id: "123456",
    values: [
        {
            "value": "A",
            "begin":0,
            "end":1,
        }
        {
            "value": "B",
            "begin":1,
            "end":2,
        }
        {
            "value": "C",
            "begin":3,
            "end":7,
        }
    ],
    "name": "test"
}

And i want to count only the "value" in "values". With some help i got the following aggregation:

db.collection.aggregate([
  {$unwind: "$values"},
  {$group: {_id: "$values.value", count: {$sum: 1}}}
])

The problem is: it takes me about 20 seconds to get the result for 6k documents. Is there anything i can do for optimication?

Greetings

Wernfried Domscheit · Answer

You can use $reduce to make some pre-processing of the arrays. Then you can sum up:

db.collection.aggregate([
   {
      $project: {
         values: {
            $reduce: {
               input: { $setUnion: "$values.value" },
               initialValue: [],
               in: {
                  $concatArrays: [
                     "$$value",
                     [{
                        value: "$$this",
                        count: {
                           $size: {
                              $filter: {
                                 input: "$values",
                                 cond: { $eq: ["$$values.value", "$$this"] },
                                 as: "values"
                              }
                           }
                        }
                     }]
                  ]
               }
            }
         }
      }
   },
   { $unwind: "$values" },
   { $group: { _id: "$values.value", count: { $sum: "$values.count" } } }
])

Often $map if faster than $reduce (actually $concatArrays makes it slow, see Performance issue for $reduce vs. $map), try this one:

db.collection.aggregate([
   {
      $project: {
         values: {
            $map: {
               input: { $setUnion: "$values.value" },
               as: "value",
               in: {
                  value: "$$value",
                  count: {
                     $size: {
                        $filter: {
                           input: "$values",
                           cond: { $eq: ["$$this.value", "$$value"] },
                        }
                     }
                  }
               }
            }
         }
      }
   },
   { $unwind: "$values" },
   { $group: { _id: "$values.value", count: { $sum: "$values.count" } } }
])

If you know the values beforehand then you can use input: ["A", "B", "C"]

instead of input: { $setUnion: "$values.value" }

MongoDB slow $unwind and $group aggregation

1 Answers