How get and use list of fields' name in MongoDB?

user_4529373 0
Viewed 325

I store price changes data for each date in MongoDB as following:

{ "_id" : "A1",
  "Price" : 
     {
         "2020-08-25": {"P" : [1200, 1300, 1250]},
         "2020-08-26": {"P" : [1310, 1400, 1200]},
         "2020-08-27": {"P" : [1500, 1300, 1300]},
         ...
     }
 
 },
 { "_id" : "A2",
   "Price" :
     {
          "2020-08-25": {"P" : [1200, 1300, 1250]},
         "2020-08-26": {"P" : [1310, 1400, 1200]},
         "2020-08-27": {"P" : [1500, 1300, 1300]},
         ...
     }
 }

Now, I want to get maximum price for all dates. How I can do it without writing each date field in my query? I mean, for a specific date like as "2020-08-25", someone can uses $group and $max to obtain maximum price value in that date. But, how write a query to get maximum price value among all dates?
Thanks

aggregation-framework mongodb-query mongodb
2 Answers

Update:

To get across the collection

db.collection.aggregate([
  {
    $project: {
      "prices": {
        "$objectToArray": "$Price"
      }
    }
  },
  {
    $group: {
      "_id": null,
      P: {
        $addToSet: {
          $max: "$prices.v.P"
        }
      }
    }
  },
  {
    $project: {
      "maxPrice": {
        $max: {
          $first: "$P"
        }
      }
    }
  }
])

play

Date specific: playground

db.collection.aggregate([
  {
    $project: {
      "prices": {//reshape it to perform object wise operations - mainly converting to array
        "$objectToArray": "$Price"
      }
    }
  },
  {//getting one by one entries
    "$unwind": "$prices"
  },
  {
    $group: {//grouping by date and getting the max from local entry
      "_id": "$prices.k",
      "values": {
        $max: "$prices.v.P"
      }
    }
  },
  {
    $project: {
      "date": "$_id",
      "maxPrice": {//getting the max across multiple entries
        $max: "$values"
      },
      "_id": 0
    }
  }
])
user_26941840

To get the maximum price for all dates in the collection you need to run an aggregate operation that first gets the maximum for each document and this can be achieved using the $max and $map operators to create an array of price values which can be easily extrapolated for a maximum value.

To get the array values you first need to convert the Price document to an array of key/value pairs of dates and the prices using $objectToArray

db.getCollection('collection').aggregate([
    { '$set': {
       'maxPricePerDocument':  {
           '$max': {
               '$map': {
                   'input': { '$objectToArray': '$Price' },
                   'in': { '$max': '$$this.v.P' }
               }
           }
       }
    } },
    { '$group': {
        '_id': 0,
        'maxPriceForEntireCollection': { '$max': '$maxPricePerDocument' }
    } }
])
user_1220050
Related