使用 DBRef 进行 MongoDB 聚合 [英] MongoDB Aggregation with DBRef

问题描述

是否可以聚合通过 DBRef 存储的数据?

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")
}

诀窍是source"本质上是多态的——它可能是不同的 $ref 值，例如webpage"、call_center"等，它们也有不同的 ObjectId.例如 DBRef("webpage", ObjectId("1")) 和 DBRef("webpage",ObjectId("2")) 将是交易发起的两个不同网页.

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.

推荐答案

Mongo 4. 通过以下方式解决了这个问题:具有这种结构:

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)
    },
    ...
    ...
}

我需要使用owner.$id"字段.但是由于字段名称中的$"，我无法使用聚合.我使用以下代码段转换了owner.$id"->owner":

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}}
])

这里有详细的解释 - https://dev.to/saurabh73/mongodb-using-aggregation-pipeline-to-extract-dbref-using-lookup-operator-4ekl

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

这篇关于使用 DBRef 进行 MongoDB 聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！