How to get Max and Min Value in MongoDB?

Question

I want to get the max and min value of the emp salary based on currency. every employee has a salary range based on currency also all the details in response should be unique. while I am using aggregation function min and max but it fetches the max and min value of salary amount but I need to get max and min based on currency field.

Sample Data:

[
  {
    "id": "1",
    "emp_name": "emp1",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city1",
        "salary": [
          {
            "INR": 5000
          },
          {
            "DOLLER": 600
          },
          {
            "MXN": 200
          }
        ]
      },
      {
        "emp_country": "country1",
        "emp_city": "city2",
        "salary": [
          {
            "INR": 600
          },
          {
            "DOLLER": 200
          },
          {
            "MXN": 400
          }
        ]
      }
    ]
  },
  {
    "id": "2",
    "emp_name": "emp2",
    "data": [
      {
        "emp_country": "country2",
        "emp_city": "city2",
        "salary": [
          {
            "INR": 5000
          },
          {
            "MXN": 200
          },
          {
            "DOLLER": 400
          }
        ]
      }
    ]
  },
  {
    "id": "3",
    "emp_name": "emp3",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city1",
        "salary": [
          {
            "MXN": 400
          }
        ]
      }
    ]
  },
  {
    "id": "4",
    "emp_name": "emp4",
    "data": [
      {
        "emp_country": "country1",
        "emp_city": "city2",
        "salary": [
          {
            "DOLLER": 200
          }
        ]
      }
    ]
  }
]

Expected Output: city, country, the name should be unique and salary have max and min based on currency.

[
  {
    "emp_city": "city1",
    "emp_country": "country1",
    "emp_name": "emp1",
    "emp_salary": [{
      "currency": "INR",
      "max": 5000,
      "min": 600
    },
    {
      "currency": "DOLLER",
      "max": 600,
      "min": 200
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 200
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country1",
    "emp_name": "emp1",
    "emp_salary":[ {
      "currency": "DOLLER",
      "max": 5000,
      "min": 200
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 400
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country2",
    "emp_name": "emp2",
    "emp_salary": [{
      "currency": "INR",
      "max": 5000,
      "min": 5000
    },
    {
      "currency": "MXN",
      "max": 400,
      "min": 200
    }]
  },
  {
    "emp_city": "city1",
    "emp_country": "country1",
    "emp_name": "emp3",
    "emp_salary": [{
      "currency": "MXN",
      "max": 400,
      "min": 400
    }]
  },
  {
    "emp_city": "city2",
    "emp_country": "country1",
    "emp_name": "emp4",
    "emp_salary": [{
      "currency": "DOLLER",
      "max": 200,
      "min": 200
    }]
  }
]

user_10770370 · Answer

We can $group twice to achieve this, once for each employee x currency to calculate the min and max values, then again without the currency to construct the final structure, like so:

db.collection.aggregate([
  {
    $unwind: "$data"
  },
  {
    $unwind: "$data.salary"
  },
  {
    $addFields: {
      currency: {
        "$objectToArray": "$data.salary"
      }
    }
  },
  {
    $unwind: "$currency"
  },
  {
    $group: {
      _id: {
        country: "$data.emp_country",
        city: "$data.emp_city",
        emp_name: "$emp_name",
        currency: "$currency.k"
      },
      max: {
        $max: "$currency.v"
      },
      min: {
        $min: "$currency.v"
      },
      
    }
  },
  {
    $group: {
      _id: {
        country: "$_id.country",
        city: "$_id.city",
        emp_name: "$_id.emp_name",
        
      },
      emp_salary: {
        $push: {
          currency: "$_id.currency",
          max: "$max",
          min: "$min"
        }
      }
    }
  },
  {
    $project: {
      emp_salary: 1,
      emp_country: "$_id.country",
      emp_city: "$_id.city",
      emp_name: "$_id.emp_name",
      _id: 0
    }
  }
])

Mongo Playground

How to get Max and Min Value in MongoDB?

1 Answers