CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Oden
Oden

Reputation: 754

How to aggregate a collection and find min/max values

I have an "orders" collection which is like this:

{ typeID: 1, buyOrder: true, price: 100 },
{ typeID: 1, buyOrder: false, price: 120 },
{ typeID: 1, buyOrder: false, price: 130 },
{ typeID: 1, buyOrder: false, price: 250 },
{ typeID: 2, buyOrder: true, price: 500 },
{ typeID: 2, buyOrder: false, price: 610 },
{ typeID: 2, buyOrder: false, price: 690 },
{ typeID: 2, buyOrder: false, price: 590 }

and I want to aggregate this collection and find the best buy/sell price for each typeid.

The result should be:

{ typeID: 1, bestBuy: 100, bestSell: 120 }
{ typeID: 2, bestBuy: 500, bestSell: 610 }

Define bestBuy / bestSell

bestBuy  = (buyOrder = true && max price)
bestSell = (buyOrder = false && min price)

This is what I have so far but I know that its wrong. Any ideas ? 

db.orders.aggregate([
    { $sort : { typeID : 1 }},
    { $group: 
        { _id: { typeID : "$typeID", buyOrder : "$buyOrder"},
        price: { $max: "$price" },
        }
     },
     { $project:
         { _id: 0,
             typeID: "$_id.typeID",
             price: "$price",
             buyOrder: "$_id.buyOrder",
         }
     }
    ])

Thanks for your time.

Upvotes: 2

Views: 6503

Answers (2)

Neil Lunn
Neil Lunn

Reputation: 151112

You may not yet be aware of the $cond operator which works as a ternary condition. So basically if a condition given a as a first argument is true then use the value in the next argument. If the condition evaluates to false then use the value in the last condition in the operator.

This turns out to be perfect as you already have an indicator of true or false to determine the field

db.orders.aggregate([
    { "$project": {
        "typeID": 1,
        "bestBuy": { "$cond": [
            "$buyOrder",
            "$price",
            null
        ]},
        "bestSell": { "$cond": [
            "$buyOrder",
            null,
            "$price"
        ]}
    }},
    { "$group": {
        "_id": "$typeID",
        "bestBuy": { "$max": "$bestBuy" },
        "bestSell": { "$min": "$bestSell" }
    }},
    { "$sort": { "_id": 1 } }
])

So the use of $max and $min here can negate the null values in the results where the condition was not met.

Upvotes: 3

BaptisteL
BaptisteL

Reputation: 499

Maybe with a mapreduce you can achieve this with something like that :

var mapFunction1 = function() {
 emit(this.typeID , this.buyOrder, this.price);
};

var reduceFunction1 = function(key, values) {
 reducedValue = { bestBuy: 0, bestSell: 0 };
 for (var idx = 0; idx < values.length; idx++) {
  if(values[idx].buyOrder && reducedValue.bestBuy < values[idx].price) {
   reducedValue.bestBuy = values[idx].price
  }
  if(!values[idx].buyOrder && reducedValue.bestSell > values[idx].price) {
   reducedValue.bestSell = values[idx].price
  }
 }
 return reducedValue;
};

db.orders.mapReduce(
 mapFunction1,
 reduceFunction1,
 { out: "your_result" }
)

Hope it helped

Upvotes: 0

Related Questions