Indexing in the mongodb pipeline

MongoDB Atlas Atlas Search
,
1

[
{
$match: {
mode: “cash”,
createdAt: {
$gte: ISODate(“2023-12-18T18:30:00.000Z”),
$lte: ISODate(“2023-12-19T18:29:59.999Z”),
},
gameType: {
$in: [“Points”],
},
},
},
{
$sort: {
createdAt: -1,
},
},
{
$group: {
_id: “$tableGenerationId”,
unique_id: {
$first: {
$ifNull: [
“$playersList.unique_id”,
“$tableHistory.playersList.unique_id”,
],
},
},
tableGenerationId: {
$first: “$tableGenerationId”,
},
gameType: {
$first: “$gameType”,
},
transactionId: {
$first: “$transactionId”,
},
userId: {
$first: “$userId”,
},
bootValue: {
$first: “$bootValue”,
},
pointValue: {
$first: “$pointValue”,
},
bonusPercentage: {
$first: “$bonusPercentage”,
},
createdAt: {
$first: “$createdAt”,
},
commission: {
$first: “$commission”,
},
tds: {
$first: “$tds”,
},
availablePlayers: {
$first: “$availablePlayers”,
},
minimumSeats: {
$first: “$minimumSeats”,
},
round: {
$first: “$round”,
},
roundList: {
$push: {
_id: “$_id”,
round: “$round”,
},
},
},
},
{
$skip: 0,
},
{
$limit: 20,
},
{
$addFields: {
convertedBootValue: {
$toDouble: “$bootValue”,
},
convertedCommission: {
$toDouble: “$commission”,
},
},
},
{
$project: {
userId: {
$arrayElemAt: [“$userId”, 0],
},
unique_id: 1,
gameId: “$transactionId”,
bootValue: 1,
minimumSeats: 1,
pointValue: 1,
tableGenerationId: 1,
bonusPercentage: 1,
commission: 1,
tds: 1,
createdAt: 1,
gameType: 1,
availablePlayers: 1,
round: 1,
transactionId: 1,
roundList: 1,
commission_amount: {
$multiply: [
“$convertedBootValue”,
“$convertedCommission”,
],
},
},
},
{
$addFields: {
total_commission_amount: {
$divide: [“$commission_amount”, 100],
},
},
},
]

May you suggest me indexng in this pipeline

2

Read code markup and update your post.

1 Like
3

Hi Sagar,

You should try to follow the ESR rule ( equality, sort and range ) when optimizing a query or aggregation pipeline, as it suggests to build compound indexes with the order of equality first then sort then range.

Based on the $match stage of this pipeline, you are filtering on the field mode on equality, createdAt on range, and gameType with $in operator which is considered a range operator here as there is a sort stage on createdAt.

If we follow the ESR to create an index that supports this aggregation, it would be as below:

{mode:1, createdAt:1, gameType:1}

This index should make it efficient to filter the collection based on the $match stage and it should also avoid having an in-memory sort stage as the index could be used for sorting as well.

I highly recommend that you take a couple of minutes to read more about the ESR rule in this documentation link, it will help you understanding how to build efficient indexes to support your workload patterns.

Regards,
Mohamed Elshafey