I have a collection "product_reviews" with this document structure

{
  _id: 'B000000OE4',
  'product/title': 'Working Class Hero',
  'product/price': '16.99',
  reviews: [
    {
      'review/userId': 'unknown',
      'review/profileName': 'unknown',
      'review/helpfulness': '2/3',
      'review/score': '4.0',
      'review/time': '27/05/1999/00:00:00',
      'review/summary': 'Worth it for one song',
      'review/text': "I really like Joan Baez'..."
    },
    {
      'review/userId': 'A1W0RKM6J6J73L',
      'review/profileName': 'Aaron Woodin ([email protected])',
      'review/helpfulness': '1/1',
      'review/score': '3.0',
      'review/time': '09/02/1999/00:00:00',
      'review/summary': 'The critical lambasting on the Amazon Page Missed one thing.',
      'review/text': "They forgot to mention Mary Chapin..."
    }, 
    ...
  ]
}

My goal is to add object for each product (each product has unique _id) that will have following structure:

{
    avgReviewScore: 4.5
    reviewsCount: 105
    reviewScoreDistrib: {
        1: 15
        2:  0
        3: 30
        4: 40
        5: 20
    }
}

I tried numerous aggregation pipelines but couldn't find a solution.

CodePudding user response：

You can try this code:

db.product_reviews.aggregate([{
    $unwind: "$reviews"
  },
  {
    $group: {
      _id: "$_id",
      avgReviewScore: {
        $avg: "$reviews.review/score"
      },
      reviewsCount: {
        $sum: 1
      },
      scores: {
        $push: "$reviews.review/score"
      }
    }
  },
  {
    $project: {
      avgReviewScore: 1,
      reviewsCount: 1,
      reviewScoreDistrib: {
        $arrayToObject: {
          $map: {
            input: [1, 2, 3, 4, 5],
            as: "num",
            in: {
              k: {$toString: "$$num"},
              v: {
                $size: {
                  $filter: {
                    input: "$scores",
                    as: "s",
                    cond: {
                      $eq: ["$$s", "$$num"]
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $merge: {
      into: "product_reviews",
      on: "_id"
    }
  }
])

If you have any issue, you can ask

CodePudding user response：

No need to $unwind and $group again (which can be very inefficient). You can use a simple updateMany:

db.collection.updateMany({},
  [
  {$set: {
      reviewsData: {$map: {
          input: "$reviews.review/score",
          in: {$toDouble: "$$this"}
      }}
  }},
  {$set: {
      reviewScoreDistrib: {
        $arrayToObject: {$map: {
            input: {$range: [1, 6]},
            as: "num",
            in: {
              k: {$toString: "$$num"},
              v: {$size: {$filter: {
                    input: "$reviewsData",
                    cond: {$eq: ["$$this", "$$num"]}
              }}}
            }
        }}
      },
      avgReviewScore: {$avg: "$reviewsData"},
      reviewsCount: {$size: "$reviewsData"}
  }}
])

See how it works on the playground example