How to improve MongoDB aggregation with group and accumulator stages?

Question

The query below:

match a selection of docs with a match
group by day of month and then using an accumulator add to the returned dataset the URLs and the counts

The problem is that the query is pretty complex and as the data grows in volume this really doesn't seem very performance. Is there is an easier way to achieve the same thing in mongodb?

The output shape looks like:

{
  "results": [
    {
      "_id": {
        "month": 2,
        "day": 2,
        "year": 2021
      },
      "urls": [
        {
          "url": "https://shop.mydomain.com/product/golden-axe",
          "count": 20
        },
        {
          "url": "https://shop.mydomain.com/product/phantasy-star",
          "count": 218
        },
        {
          "url": "https://shop.mydomain.com/product/sega-arcades-retro",
          "count": 30
        }
      ],
      "count": 268
    },
    {
      "_id": {
        "month": 2,
        "day": 3,
        "year": 2021
      },
      "urls": [
        {
          "url": "https://shop.mydomain.com/product/golden-axe",
          "count": 109
        },
        {
          "url": "https://shop.mydomain.com/product/phantasy-star",
          "count": 416
        },
        {
          "url": "https://shop.mydomain.com/product/sega-arcades-retro",
          "count": 109
        }
      ],
      "count": 634
    },

        const aggregate = [
      {
        $match: {
          source: 'itemLineView',
          createdAt: {
            $gte: new Date(query.dateGT),
            $lte: new Date(query.dateLT)
          },
          url: { $regex: `^${query.url}` },
        }
      },
      {
        $group: {
          _id: {
            month: {
              $month: '$createdAt'
            },
            day: {
              $dayOfMonth: '$createdAt'
            },
            year: {
              $year: '$createdAt'
            }
          },
          urls: {
            // https://docs.mongodb.com/manual/reference/operator/aggregation/accumulator/#grp._S_accumulator
            $accumulator: {
              init: function (): AccumulatorSourceStats {
                return {
                  origins: []
                };
              },
              // initArgs: [], // Argument arr to pass to the init function
              accumulate: function (state: AccumulatorSourceStats, url: string) {
                const index = state.origins.findIndex(function (origin) {
                  return origin.url === url;
                });
                if (index === -1) {
                  state.origins.push({
                    url: url,
                    count: 1
                  });
                } else {
                  ++state.origins[index].count;
                }
                return state;
              },

              accumulateArgs: ['$url'], // Argument(s) passed to the accumulate function

              merge: function (state1: AccumulatorSourceStats, state2: AccumulatorSourceStats) {
                return {
                  origins: state1.origins.concat(state2.origins)
                };
              },

              finalize: function (state: AccumulatorSourceStats) { // Adjust the state to only return field we need
                const sortByUrl = function (a: AccumulatorSourceStatsOrigin, b: AccumulatorSourceStatsOrigin) {
                  if (a.url < b.url) {
                    return -1;
                  }
                  if (a.url > b.url) {
                    return 1;
                  }
                  return 0;
                };
                return state.origins.sort(sortByUrl);
              },

              lang: 'js'
            }
          },
          count: { $sum: 1 }
        }
      },
      { $sort: { _id: 1 } }
    ];
    return this.model.aggregate(aggregate);

user_10770370 · Answer

From the docs:

Executing JavaScript inside of an aggregation operator may decrease performance. Only use the $accumulator operator if the provided pipeline operators cannot fulfill your application’s needs.

It is considered good practice to avoid using javascript code within a Mongo pipeline, this should be used only as a last resort. And in this case we can avoid using it by just $grouping twice, once per url per day and then once per day. like so:

db.collection.aggregate([
  {
    $match: {
      source: "itemLineView",
      createdAt: {
        $gte: new Date(query.dateGT),
        $lte: new Date(query.dateLT)
      },
      url: {
        $regex: `^${query.url}`
      },
      
    }
  },
  {
    $group: {
      _id: {
        month: {
          $month: "$createdAt"
        },
        day: {
          $dayOfMonth: "$createdAt"
        },
        year: {
          $year: "$createdAt"
        },
        url: "$url"
      },
      count: {
        $sum: 1
      }
    }
  },
  {
    $group: {
      _id: {
        month: "$_id.month",
        day: "$_id.day",
        year: "$_id.year",
        
      },
      urls: {
        $push: {
          url: "$_id.url",
          count: "$count"
        }
      },
      count: {
        $sum: "$count"
      }
    }
  }
])

Mongo Playground

How to improve MongoDB aggregation with group and accumulator stages?

1 Answers