MongoDB retrive max value from different fields

Question

What is the way to retrieve maximum (or minimum) value of different fields for each document?

The collection is look like the following:

{
    "_id" : "AQ",
    "arr_delay" : 262421.0,
    "carrier_delay" : 144922.0,
    "weather_delay" : 2297.0,
    "nas_delay" : 15933.0,
    "security_delay" : 1871.0,
    "late_aircraft_delay" : 97398.0
}
{
    ...
}

For each document in the collection I need to find the field with the minimum value compared to the other fields in the document

Answer 1

You can try this,

• $project fields to create a array of fields

db.collection.aggregate([
  {
    $project: {
      fields: {
        $objectToArray: "$$ROOT"
      }
    }
  },

• $unwind deconstruct fields array

  { $unwind: "$fields" },

• $match don't include _id field, you can add more fields if you want to remove from this comparison

  {
    $match: {
      "fields.k": { $ne: "_id" }
    }
  },

• $group by _id
• $max to get max from fields in maxValue
• $min to get min from fields in minValue

  {
    $group: {
      _id: "$_id",
      maxValue: { $max: "$fields.v" },
      minValue: { $min: "$fields.v" }
    }
  }
])

Playground

Answer 2

You need to use $objectToArray and iterate over the docs keys like so:

db.collection.aggregate([
  {
    "$addFields": {
      minField: {
        $reduce: {
          input: {
            "$filter": {
              "input": {
                "$objectToArray": "$$ROOT"
              },
              as: "field",
              cond: {
                "$setIsSubset": [
                  [
                    {
                      $type: "$$field.v"
                    }
                  ],
                  [
                    "double",
                    "decimel",
                    
                  ]
                ]
              }
            }
          },
          initialValue: {
            name: null,
            minVal: null
          },
          in: {
            name: {
              $cond: [
                {
                  $lt: [
                    "$$this.v",
                    "$$value.minVal"
                  ]
                },
                "$$this.k",
                "$$value.name"
              ]
            },
            minVal: {
              $min: [
                "$$this.v",
                "$$value.minVal"
              ]
            }
          }
        }
      }
    }
  },
  {
    "$addFields": {
      "minField": "$minField.name"
    }
  }
])

MongoPlayground

If you're using Mongo v4.4+ you can also use $isNumber instead of the messy part of the $filter.