CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Gautam Gadipudi
Gautam Gadipudi

Reputation: 21

Use of something like $group inside $addFields

Below is one of my document from collection movies:

{
        "_id" : 4,
        "startYear" : 1892,
        "title" : "Un bon bock",
        "originalTitle" : "Un bon bock",
        "rating" : 6.4,
        "type" : "short",
        "numVotes" : 105,
        "genres" : [
                "Short",
                "Animation"
        ]
}

I would like every document to have a field called normalizedRating that is calculated as follows:

normalizedRating = (rating - min(rating)) / (max(rating) - min(rating))

So, I get document like: 

{
        "_id" : 4,
        "startYear" : 1892,
        "title" : "Un bon bock",
        "originalTitle" : "Un bon bock",
        "rating" : 6.4,
        "type" : "short",
        "numVotes" : 105,
        "genres" : [
                "Short",
                "Animation"
        ], 
        "normalizedRating": 6.3
}

I am able to get the above result by using two different queries. I'm curious to know if it can be done using a single query.

Upvotes: 1

Views: 2449

Answers (1)

whoami - fakeFaceTrueSoul
whoami - fakeFaceTrueSoul

Reputation: 17915

If You wanted to do it in one query, then try either one of these two :

Query 1 :

db.collection.aggregate([
  {
    $group: {
      _id: "",
      maxRating: { $max: "$rating" },
      minRating: { $min: "$rating" },
      data: { $push: "$$ROOT" },
    },
  },
  {
    $unwind: "$data",
  },
  {
    $addFields: {
      "data.normalizedRating": {
        $divide: [
          { $subtract: ["$data.rating", "$minRating"] },
          { $subtract: ["$maxRating", "$minRating"] },
        ],
      },
    },
  },
  {
    $replaceRoot: { newRoot: "$data" },
  },
]);

Test : MongoDB-playground

Query 2 :

db.collection.aggregate([
  {
    $facet: {
      data: [{ $match: {} }],
      ratingValues: [
        {
          $group: {
            _id: "",
            maxRating: { $max: "$rating" },
            minRating: { $min: "$rating" },
          },
        },
      ],
    },
  },
  {
    $unwind: "$data",
  },
  {
    $unwind: "$ratingValues",
  },
  {
    $addFields: {
      "data.normalizedRating": {
        $divide: [
          { $subtract: ["$data.rating", "$ratingValues.minRating"] },
          { $subtract: ["$ratingValues.maxRating", "$ratingValues.minRating"] },
        ],
      },
    },
  },
  {
    $project: { ratingValues: 0 },
  },
  {
    $replaceRoot: { newRoot: "$data" },
  },
]);

Test : MongoDB-playground

At end of the day if your dataset is medium one then these can perform good, but on huge datasets these might or might not work well - I would say to split this task into two to do some work in code or multiple calls if really needed or try to implement the same task using mapReduce if aggregation is really slow.

Upvotes: 1

Related Questions