MongoDB aggregation - how to get a percentage value by rate

Question

I'm trying to get information on row base all data collection

my data :

{
    _id : 1,
    age : 21,
    salary : 2500
},
{
    _id : 2,
    age : 42,
    salary : 4300
},
{
    _id : 3,
    age : 32,
    salary : 3100
},
{
    _id : 4,
    age : 18,
    salary : 7000
},
{
    _id : 5,
    age : 25,
    salary : 5600
},
{
    _id : 6,
    age : 28,
    salary : 5200
}
,
{
    _id : 7,
    age : 38,
    salary : 5000
},
{
    _id : 8,
    age : 28,
    salary : 5200
}

I want to divide the values ​​into four options.

First option is 25 percent.

Second option values ​​between 25 and 50 percent.

Third option between 50 and 75 percent and

last option between 75 and 100 percent.

So when I want to pull out an object it will show me the values ​​relative to the other values.

for example :

{
    _id : 4,
    age : 'less than 25 percent',
    salary : 'more than 75 percent'
}

Answer 1

You can do that using $bucketAuto which would by default split all the documents of a collection into no.of buckets specified most likely equally,

Steps :

1. Use $facet to create array's one for age & one for salary, each array 4 sub documents with all documents spliced equally into 4 buckets(sub-docs). Adds every mapped document into data array.
2. Unwind age & salary to get objects out along with their specific indexes.
3. Use $addFields to replacing existing fields age & salary with texts respective to their positions in buckets.
4. Use $project & $concatArrays to merge two arrays in to single array named data.
5. unwind data array & $group on _id adding unique texts to age & salary.
6. age & salary are array of single elements(string) so by using $arrayElemAt getting first element to make age & salary as fields with type string.

Query :

db.collection.aggregate([
    {
        $facet: {
            "age": [
                {
                    $bucketAuto: {
                        groupBy: "$age",
                        buckets: 4,
                        output: {
                            "data": { $push: { '_id': "$$ROOT._id", age: "$$ROOT.age" } }
                        }
                    }
                }
            ],
            "salary": [
                {
                    $bucketAuto: {
                        groupBy: "$salary",
                        buckets: 4,
                        output: {
                            "data": { $push: { '_id': "$$ROOT._id", salary: "$$ROOT.salary" } }
                        }
                    }
                }
            ]
        }
    }, { $unwind: { path: '$age', includeArrayIndex: "arrayIndexAge" } },
       { $unwind: { path: '$salary', includeArrayIndex: "arrayIndexSalary" } },
    {
        $addFields: {
            'age.data.age': {
                $switch: {
                    branches: [
                        { case: { $eq: ['$arrayIndexAge', 0] }, then: "less than 25 percent" },
                        { case: { $eq: ['$arrayIndexAge', 1] }, then: "​​between 25 and 50 percent" },
                        { case: { $eq: ['$arrayIndexAge', 2] }, then: "between 50 and 75 percent" },
                        { case: { $eq: ['$arrayIndexAge', 3] }, then: "more than 75 percent" }
                    ]
                }
            },
            'salary.data.salary': {
                $switch: {
                    branches: [
                        { case: { $eq: ['$arrayIndexSalary', 0] }, then: "less than 25 percent" },
                        { case: { $eq: ['$arrayIndexSalary', 1] }, then: "​​between 25 and 50 percent" },
                        { case: { $eq: ['$arrayIndexSalary', 2] }, then: "between 50 and 75 percent" },
                        { case: { $eq: ['$arrayIndexSalary', 3] }, then: "more than 75 percent" }
                    ]
                }
            }
        }
    }, { $project: { data: { $concatArrays: ['$age.data', '$salary.data'] } } }, { $unwind: '$data' },
    { $group: { _id: '$data._id', age: { $addToSet: '$data.age' }, salary: { $addToSet: '$data.salary' } } },
    { $addFields: { age: { $arrayElemAt: ['$age', 0] }, salary: { $arrayElemAt: ['$salary', 0] } } }])

Test : MongoDB-Playground

Answer 2

Take a look if this meets your requirements.

Explanation

Mongodb aggregation offers more analytical operations to compute data. With $facet operation we can compute several aggregations in one.

We calculate min / max values for salary and age. Then we calculate percentage for each salary and age with this formula

 x  − min
---------- x 100
max − min

With $switch we define 4 options: less than 25, less than 50, less than 75 and more han 75

---

db.collection.aggregate([
  {
    $facet: {
      extremes: [
        {
          $group: {
            _id: null,
            maxAge: {
              $max: "$age"
            },
            minAge: {
              $min: "$age"
            },
            maxSalary: {
              $max: "$salary"
            },
            minSalary: {
              $min: "$salary"
            }
          }
        }
      ],
      root: [
        {
          $match: {}
        }
      ]
    }
  },
  {
    $set: {
      extremes: {
        $arrayElemAt: [
          "$extremes",
          0
        ]
      }
    }
  },
  {
    $unwind: "$root"
  },
  {
    $addFields: {
      root: {
        agePercent: {
          $toInt: {
            $multiply: [
              {
                $divide: [
                  {
                    $subtract: [
                      "$root.age",
                      "$extremes.minAge"
                    ]
                  },
                  {
                    $subtract: [
                      "$extremes.maxAge",
                      "$extremes.minAge"
                    ]
                  }
                ]
              },
              100
            ]
          }
        },
        salaryPercent: {
          $toInt: {
            $multiply: [
              {
                $divide: [
                  {
                    $subtract: [
                      "$root.salary",
                      "$extremes.minSalary"
                    ]
                  },
                  {
                    $subtract: [
                      "$extremes.maxSalary",
                      "$extremes.minSalary"
                    ]
                  }
                ]
              },
              100
            ]
          }
        }
      }
    }
  },
  {
    $replaceWith: "$root"
  },
  {
    $project: {
      age: {
        $switch: {
          branches: [
            {
              case: {$lt: ["$agePercent",25]},
              then: "less than 25 percent"
            },
            {
              case: {$lt: ["$agePercent",50]},
              then: "less than 50 percent"
            },
            {
              case: {$lt: ["$agePercent",75]},
              then: "less than 75 percent"
            },
            {
              case: {$gte: ["$agePercent",75]},
              then: "more than 75 percent"
            }
          ],
          default: "Unknown"
        }
      },
      salary: {
        $switch: {
          branches: [
            {
              case: {$lt: ["$salaryPercent",25]},
              then: "less than 25 percent"
            },
            {
              case: {$lt: ["$salaryPercent",50]},
              then: "less than 50 percent"
            },
            {
              case: {$lt: ["$salaryPercent",75]},
              then: "less than 75 percent"
            },
            {
              case: {$gte: ["$salaryPercent",75]},
              then: "more than 75 percent"
            }
          ],
          default: "Unknown"
        }
      }
    }
  }
])

MongoPlayground