CODEWITHSUNDEEP
arraysmongodbgroup-bymongodb-queryaggregation-framework
Andrija Novakovic
Andrija Novakovic

Reputation: 113

How to group values by timestamp

For every entity in database I have array of timestamps with prices, I want to group them by timestamp and get average price for that timestamp.

I am currently using mongoose, and I tried various functions for grouping by, but I did not find solution to group by every element.

{ 
 _id : "52b632a9e4f2ba13c82ccd23", 
 history-dates : [2019-10-1, 2019-10-2, 2019-10-3, 2019-10-4]
 history-prices : [1000.0, 2000.0, 500.0, 1500.0]
}, 

{ 
 _id : "52b632a9e4f2ba13c82ccd23", 
 history-dates : [2019-10-1, 2019-10-2, 2019-10-3, 2019-10-4]
 history-prices : [2000.0, 3000.0, 1500.0, 2500.0]
}

I want to have result like:

date: 2019-10-1
avgPrice : 1500.0

How can I achieve this and thanks in advance.

Upvotes: 0

Views: 90

Answers (1)

invider
invider

Reputation: 353

As Neil pointed out that you should change the document structure a little bit

So introduce a field historicalPrices with array type having structure as : [{ date: "2019-10-1", price: 1000.0 }]

Then you can apply following query to get the average price grouped by date

db.mycollection.aggregate([
  { $unwind: "historicalPrices" },
  { $group: { 
      "_id": "$historicalPrices.date",
      avgPrice:{ $avg: "$historicalPrices.price" }
      } 
  }
])
//Result: [{ _id: "2019-10-01", avgPrice: 1500.0 }, ....]

Know more about Aggregation Pipeline, $unwind, $group, $avg

Upvotes: 2

Related Questions