Aggregating MongoDB with DBRef
Is it possible to aggregate data that is stored via DBRef?
Mongo 2.6
Let's say I have transaction data like:
{
_id : ObjectId(...),
user : DBRef("user", ObjectId(...)),
product : DBRef("product", ObjectId(...)),
source : DBRef("website", ObjectId(...)),
quantity : 3,
price : 40.95,
total_price : 122.85,
sold_at : ISODate("2015-07-08T09:09:40.262-0700")
}
The source trick is polymorphic in nature - it can be different $ ref values like webpage, call_center, etc., which also have different ObjectIds. For example, DBRef ("webpage", "ObjectId" ("1")) and DBRef ("webpage", ObjectId ("2")) will be two different web pages where the transaction originated.
I would like to eventually aggregate across sources over a period of time (like a month):
db.coll.aggregate( { $match : { sold_at : { $gte : start, $lt : end } } },
{ $project : { source : 1, total_price : 1 } },
{ $group : {
_id : { "source.$ref" : "$source.$ref" },
count : { $sum : $total_price }
} } );
The trick is that you are getting a path error trying to use a variable starting with $, either by grouping it or by converting with expressions across the project.
How to do it? Actually trying to drag and drop this data through aggregation into a subset to work there. Trying to avoid a big cursor operation on millions of records to transform data so I can fill it.
source to share
You cannot use DBRef
values with an aggregation framework. Instead, you need to use JavasScript's mapReduce processing to access the property names they are using:
db.coll.mapReduce(
function() {
emit( this.source.$ref, this["total_price"] )
},
function(key,values) {
return Array.sum( values );
},
{
"query": { "sold_at": { "$gte": start, "$lt": end } },
"out": { "inline": 1 }
}
)
You really shouldn't be using DBRef
at all. The usage is now deprecated, and if you think you need an external reference, then you must "manually reference" this in your own code, or implement some other library with which you can do this in a much more convenient way.
source to share