Mongo query based on count and if count is same then alphabetically-CodePudding

I have a coupons collections and I am trying to group all the coupons based on the code. After that I want to sort them on the basis of the count. Till this the query works fine. But after this if the count is same for coupon codes then it should sort them on the basis of another key. Here I am trying to sort it on the basis of the coupon code. But every time I run the query the order is not same and it is not being sorted properly on the basis of coupon code. I am attaching the mongoose query

const coupons = await Coupons.aggregate([
  { $match: couponFilter },
  {
    $group: {
      _id: "$code",
      count: { $sum: 1 },
    },
  },
  { $sort: { count: -1, code: 1 } },
  { $skip: skip },
  { $limit: limit },
  { $project: { code: "$_id", count: 1, _id: 0 } },
]);

Normally it should work fine but it does not work correctly. Each time I run this query it returns me the items in different order. Although, the items are sorted correctly on the basis of count but when some coupons have same count they are not sorted properly on the basis if coupon code.

Not able to attach the returned data as text so attaching it as image. These are the screenshots of the data returned when I executed the query 3 times and each time the order of coupon codes with same number of count is different.

CodePudding user response：

The $sort stage is invalid as the field code is not included in the $group. The field code does not exist on the documents after the $group stage. Therefore, only the first half of the sort condition works.

Updated pipeline with changes in the $sort stage:

const coupons = await Coupons.aggregate([
  { $match: couponFilter },
  {
    $group: {
      _id: "$code",
      count: { $sum: 1 },
    },
  },
  { $sort: { count: -1, _id: 1 } },
  { $skip: skip },
  { $limit: limit },
  { $project: { code: "$_id", count: 1, _id: 0 } },
]);

CodePudding user response：

After going through mongo docs this is the final query that solved my issue

const coupons = await Coupons.aggregate([
  { $match: couponFilter },
  {
    $group: {
      _id: "$code",
      count: { $sum: 1 },
    },
  },
  {
    $project: {
      code: "$_id",
      count: 1,
      _id: 0,
      lowerCode: { $toLower: "$_id" },
    },
  },
  { $sort: { count: -1, lowerCode: 1 } },
  { $skip: skip },
  { $limit: limit },
]);