CODEWITHSUNDEEP
node.jsmongodbmongodb-queryaggregation-frameworkaggregate
KAMAL
KAMAL

Reputation: 158

Want to write mongodb aggregate to calculate the average rating of product

I have 2 different collection

  1. Product collection having 3 field => _id , name ,postedBy
  {
    "_id": "61eac83938ba2c41e626ac81", // mongoID
    "name":"OLD AC",//string
   "postedBy":"61c9a23dee9e923c1bf6d277" // mongoID
  }
  1. Profile collection having 3 field => _id , name,reviews => []
{
  "_id": "61eac83938ba2c41e626ac81", // mongoID
  "name":"rahul rj",//string,
  "reviews": [
      {
        "_id": "6280f9e5bd0ba79c387ee951",
        "rating": 2,
        "itemId": "61eac83938ba2c41e626ac81",
        "updatedAt": "2022-05-15T13:02:29.408Z",
        "createdAt": "2022-05-15T13:02:29.408Z"
      },
      {
        "_id": "6280f9e5bd0ba79c387ee951",
        "rating": 2,
        "itemId": "61eac83938ba2c41e626ac85",
        "updatedAt": "2022-05-15T13:02:29.408Z",
        "createdAt": "2022-05-15T13:02:29.408Z"
      }
  ]
}

I WANT TO JOIN PRODUCT AND PROFILE COLLECTION AND GET THE AVERAGE RATING OF PRODUCT

https://mongoplayground.net/p/iTW4-mFgH2U. (Please find the url for document)

I want to create a aggregate query to fetch the rating from profile collection and select it rating when we are selecting product list

Upvotes: 0

Views: 49

Answers (1)

nimrod serok
nimrod serok

Reputation: 16033

If I understand you correctly, you can use $lookup with pipeline for this:

db.product.aggregate([
  {$lookup: {
      from: "profile",
      let: {postedBy: "$postedBy", id: "$_id"},
      pipeline: [
        {$match: {$expr: {$eq: ["$$postedBy", "$_id"]}}},
        {$project: {
            _id: 0,
            rating: {
              $avg: {
                $reduce: {
                  input: "$reviews",
                  initialValue: [],
                  in: {$concatArrays: [
                      "$$value",
                      {$cond: [
                          {$eq: ["$$this.item", "$$id"]},
                          ["$$this.rating"],
                          []
                      ]}
                  ]}
                }
              }
            }
        }}
      ],
      as: "avgRating"
    }
  },
  {$set: {avgRating: {$first: "$avgRating.rating"}}}
])

See how it works on the playground example

Upvotes: 1

Related Questions