Question 399492 in Mongodb Question posted in
The official documentation can be found here.

Each array should contain unique element using Facet in MongoDB

Here I’m adding the Mongo playground URL how my schema structure look like

Now I want 3 unique array elements which are mostPurchased, latestRewards and suggestedRewards.

So I’m trying to achieve by using $facet

{
    $facet: {
      mostPurchased: [
        {
          $sort: {
            buyerCount: -1
          }
        },
        {
          $limit: 15
        },
        {
          $group: {
            _id: "$_id",
            doc: {
              $first: "$$ROOT"
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: "$doc"
          }
        }
      ],
      latestRewards: [
        {
          $sort: {
            created: -1
          }
        },
        {
          $group: {
            _id: "$_id",
            doc: {
              $first: "$$ROOT"
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: "$doc"
          }
        },
        {
          $limit: 15
        }
      ],
      suggestedRewards: [
        {
          $match: {
            points: {
              $lt: 300
            }
          }
        },
        {
          $group: {
            _id: "$_id",
            doc: {
              $first: "$$ROOT"
            }
          }
        },
        {
          $replaceRoot: {
            newRoot: "$doc"
          }
        },
        {
          $limit: 15
        }
      ]
    }
  },
  {
    $project: {
      mostPurchased: 1,
      latestRewards: 1,
      suggestedRewards: 1
    }
  }

But this one producing the duplicate element on each array.

Note: All the stage aggregations should be perform before the projection so that it each array would contain 15 elements. for reference I added 8 elements on mongo playground.

Let me know where I’m going wrong

Tags:

2

Answers


  1. 0

    Use $group to aggregate unique documents based on a unique identifier.
    Apply the $facet stage to perform the required operations for each array.

    [
  {
    $group: {
      _id: "$_id",
      doc: { $first: "$$ROOT" }
    }
  },
  {
    $replaceRoot: {
      newRoot: "$doc"
    }
  },
  {
    $facet: {
      mostPurchased: [
        {
          $sort: {
            buyerCount: -1
          }
        },
        {
          $limit: 15
        }
      ],
      latestRewards: [
        {
          $sort: {
            created: -1
          }
        },
        {
          $limit: 15
        }
      ],
      suggestedRewards: [
        {
          $match: {
            points: {
              $lt: 300
            }
          }
        },
        {
          $limit: 15
        }
      ]
    }
  },
  {
    $project: {
      mostPurchased: 1,
      latestRewards: 1,
      suggestedRewards: 1
    }
  }
]
    Login or Signup to reply.
  2. 0

    There is not enough data in your sample dataset to illustrate top 15 for each groups, so this example uses top 2 to illustrate.

    The idea is to assign to each group only once. Here, we assume the priority would be:

    1. mostPurchased
    2. latestRewards
    3. suggestedRewards

    So if a document matched all groups, it would be assigned to mostPurchased group. If a document matched only group 2 and 3, it would be assigned to group 2. This behaviour is configurable and will be explained in later code.

    The aggregation pipeline would be like below:

    1. your previous $project and other stages
    2. $setWindowFields to compute mostPurchasedRank with $rank
    3. $setWindowFields to compute latestRewardsRank with $rank
    4. $set a field grouping with $switch. Here is the part where we configure the priority. Based on the calculated rankings in step 2 and 3, we assign documents into different groups.
    5. $setWindowFields to compute inGroupRanking with $rank. This helps to avoid we are having too many documents in a group(thus breaching MongoDB 16MB document size limit in later stage)
    6. $match to select inGroupRanking <= 2(i.e. document number per group, would be 15 in your original case)
      7.(optional) $group by grouping to push all docs inside.
    db.collection.aggregate([
  {
    "$project": {
      validity: "$validity",
      name: "$name.en",
      points: "$points",
      _id: "$_id",
      description: "$description.en",
      image: "$image",
      colorCode: "$colorCode",
      backgroundColorCode: {
        $ifNull: [
          "$backgroundColorCode",
          "#FF0000"
        ]
      },
      categoryId: {
        _id: "$categoryId._id",
        name: "$categoryId.name"
      },
      created: "$created",
      totalPoints: "$totalPoints",
      rewardType: "CUSTOM_REWARD",
      sponsorName: "$sponsorName",
      sponsorNameColor: {
        $ifNull: [
          "$sponsorNameColor",
          "#808080"
        ]
      },
      sponsorImage: {
        $ifNull: [
          "$sponsorImage",
          "https://livwell.s3.ap-southeast-1.amazonaws.com/web/download.jpeg"
        ]
      },
      isLinkAvailable: "$isLinkAvailable",
      discountPercent: "$discountPercent",
      hasExternalLink: "$hasExternalLink",
      buyerCount: "$totalUsers",
      buyerTextColor: "#FFFFFF",
      buyerBackgroundColorCode: "#00FF00",
      validityColorCode: {
        $ifNull: [
          "$validityColorCode",
          "#FDFDFD"
        ]
      },
      validityBackgroundColorCode: {
        $ifNull: [
          "$validityBackgroundColorCode",
          "#FF0000"
        ]
      },
      // isDealsOfTheDay: "$isDealsOfTheDay",
      // isFeatured: "$isFeatured",
      hasExternalLinkWithCode: "$hasExternalLinkWithCode",
      hasScanner: "$hasScanner",
      corporateId: "$corporateId"
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        buyerCount: -1
      },
      "output": {
        "mostPurchasedRank": {
          $rank: {}
        }
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": null,
      "sortBy": {
        created: -1
      },
      "output": {
        "latestRewardsRank": {
          $rank: {}
        }
      }
    }
  },
  {
    "$set": {
      "grouping": {
        "$switch": {
          "branches": [
            {
              "case": {
                $lte: [
                  "$mostPurchasedRank",
                  2// your per group limit here
                  
                ]
              },
              "then": "mostPurchased"
            },
            {
              "case": {
                $lte: [
                  "$latestRewardsRank",
                  2// your per group limit here
                  
                ]
              },
              "then": "latestRewards"
            },
            {
              "case": {
                $lt: [
                  "$points",
                  300
                ]
              },
              "then": "suggestedRewards"
            }
          ],
          default: null
        }
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": "$grouping",
      "sortBy": {
        "_id": 1
      },
      "output": {
        "inGroupRanking": {
          $rank: {}
        }
      }
    }
  },
  {
    "$match": {
      inGroupRanking: {
        $lte: 2// your per group limit here
        
      }
    }
  },
  {
    "$group": {
      "_id": "$grouping",
      "docs": {
        "$push": "$$ROOT"
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search