MongoDB Aggregation with DBRef

Question

Is it possible to aggregate on 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 trick is "source" is polymorphic in nature - it could be different $ref values such as "webpage", "call_center", etc that also have different ObjectIds. For example DBRef("webpage", ObjectId("1")) and DBRef("webpage",ObjectId("2")) would be two different webpages where a transaction originated.

I would like to ultimately aggregate by source 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 you get a path error trying to use a variable starting with $ either by trying to group by it or by trying to transform using expressions via project.

Any way to do this? Actually trying to push this data via aggregation to a subcollection to operate on it there. Trying to avoid a large cursor operation over millions of records to transform the data so I can aggregate it.

score 2 · Answer 1 · answered Apr 26 '20 at 21:10

Mongo 4. Solved this issue in the following way: Having this structure:

{
    "_id" : LUUID("144e690f-9613-897c-9eab-913933bed9a7"),
    "owner" : {
        "$ref" : "person",
        "$id" : NumberLong(10)
    },
    ...
    ...
}

I needed to use "owner.$id" field. But because of "$" in the name of field, I was unable to use aggregation. I transformed "owner.$id" -> "owner" using following snippet:

db.activities.find({}).aggregate([
    {
        $addFields: {
            "owner": {
                $arrayElemAt: [{ $objectToArray: "$owner" }, 1]
            }
        }   
    },
    {
        $addFields: {
            "owner": "$owner.v"
        }
    },
    {"$group" : {_id:"$owner", count:{$sum:1}}},
    {$sort:{"count":-1}}
])

Detailed explanations here - https://dev.to/saurabh73/mongodb-using-aggregation-pipeline-to-extract-dbref-using-lookup-operator-4ekl

score 1 · Answer 2 · answered Jul 09 '15 at 00:03

You cannot use DBRef values with the aggregation framework. Instead you need to use JavasScript processing of mapReduce in order to access the property naming that they use:

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 should not be using DBRef at all. The usage is basically deprecated now and if you feel you need some external referencing then you should be "manually referencing" this with your own code or implemented by some other library, with which you can do so in a much more supported way.

thanks, pretty much what I thought. I have a way around this by "flattening" via aggregation in bulk that is doing the trick better than one-by-one via a cursor. unfortunately easier said than done not using DBRef in our well-established code base :-) — Jeff, Jul 09 '15 at 00:47

MongoDB Aggregation with DBRef

2 Answers2

Linked