Mongo如何使用DBRef进行$ lookup [英] Mongo how to $lookup with DBRef

问题描述

我有麻烦了(/(ㄒoㄒ)/~~).假设集合A为

I have a trouble(/(ㄒoㄒ)/~~). Suppose that collection A is

{ 
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"), 
    "bid" : [
        DBRef("B", ObjectId("582abcd85d2dfa67f44127e0")),
        DBRef("B", ObjectId("582abcd85d2dfa67f44127e1"))
    ]
}

和馆藏B:

and Collection B:

{ 
    "_id" : ObjectId("582abcd85d2dfa67f44127e0"),  
    "status" : NumberInt(1), 
    "seq" : NumberInt(0)
},
{ 
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"), 
    "status" : NumberInt(1), 
    "seq" : NumberInt(0)
} 

我不知道如何$ lookup'bid'.我尝试过

I don't know how to $lookup the 'bid'. I tried

db.A.aggregate(
    [
        {$unwind: {path: "$bid"}},
        {$lookup: {from: "B", localField: "bid", foreignField: "_id", as: "bs"}},
    ]
) 

和

and

db.A.aggregate(
    [
        {$unwind: {path: "$bid"}},
        {$lookup: {from: "B", localField: "bid.$id", foreignField: "_id", as: "bs"}},
    ]
)

但是它不起作用.有人可以帮忙吗?谢谢.

but it doesn't work. Anybody can help? Thanks.

推荐答案

实际上，另一个答案是错误的.可以在聚合器中的DBref字段上进行查找，而您不需要为此进行mapreduce.

Actually, the other answer is wrong. It is possible to do a lookup on a DBref field within your aggregator, and you don't need mapreduce for that.

db.A.aggregate([
{
    $project: { 
        B_fk: {
          $map: { 
             input: { 
                  $map: {
                      input:"$bid",
                      in: {
                           $arrayElemAt: [{$objectToArray: "$$this"}, 1]
                      },
                  }
             },
             in: "$$this.v"}},
        }
}, 
{
    $lookup: {
        from:"B", 
        localField:"B_fk",
        foreignField:"_id", 
        as:"B"
    }
}
])

结果

{
    "_id" : ObjectId("59bb79df1e9c00162566f581"),
    "B_fk" : null,
    "B" : [ ]
},
{
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
    "B_fk" : [
        ObjectId("582abcd85d2dfa67f44127e0"),
        ObjectId("582abcd85d2dfa67f44127e1")
    ],
    "B" : [
        {
            "_id" : ObjectId("582abcd85d2dfa67f44127e0"),
            "status" : NumberInt("1"),
            "seq" : NumberInt("0")
        }
    ]
}

简短说明

使用$ map遍历DBRef，将每个DBref分成一个数组，仅保留$ id字段，然后使用$$ this.v摆脱k:v格式，仅保留ObjectId并除去所有其余的.现在，您可以在ObjectId上查找.

Short Explanation

Loop through the DBRefs with $map, break each DBref into an array, keep only the $id field, then get rid of the k:v format with $$this.v, keeping only the ObjectId and removing all the rest. You can now lookup on the ObjectId.

在聚合器中，DBRef BSON类型可以像具有两个或三个字段(ref，id和db)的对象一样进行处理.

Within the aggregator, a DBRef BSON type can be handled like an object, with two or three fields (ref, id, and db).

如果您这样做:

db.A.aggregate([
    {
        $project: { 
            First_DBref_as_array: {$objectToArray:{$arrayElemAt:["$bid",0]}},
            Second_DBref_as_array: {$objectToArray:{$arrayElemAt:["$bid",1]}},
            }

    },

])

这是结果:

{
"_id" : ObjectId("582abcd85d2dfa67f44127e1"),
"First_DBref_as_array : [
    {
        "k" : "$ref",
        "v" : "B"
    },
    {
        "k" : "$id",
        "v" : ObjectId("582abcd85d2dfa67f44127e0")
    }
],
"Second_DBref_as_array" : [
    {
        "k" : "$ref",
        "v" : "B"
    },
    {
        "k" : "$id",
        "v" : ObjectId("582abcd85d2dfa67f44127e0")
    }
]
}

将dbref转换为数组后，可以通过仅查询索引1的值来摆脱无用的字段，如下所示:

Once you have transformed a dbref into an array, you can get rid of the useless fields by querying only the value at index 1, like this:

db.A.aggregate([
    {
        $project: { 
            First_DBref_as_array: {$arrayElemAt: [{$objectToArray:{$arrayElemAt:["$bid",0]}},1]},
            Second_DBref_as_array: {$arrayElemAt: [{$objectToArray:{$arrayElemAt:["$bid",0]}},1]},
            }

    },

])

结果:

{
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
    "First_DBref_as_array" : {
        "k" : "$id",
        "v" : ObjectId("582abcd85d2dfa67f44127e0")
    },
    "Second_DBref_as_array" : {
        "k" : "$id",
        "v" : ObjectId("582abcd85d2dfa67f44127e0")
    }
}

然后，您最终可以通过指向"$ myvalue.v"来获得所需的值，就像这样

Then you can get finally to the value you want by pointing to "$myvalue.v", just like this

db.A.aggregate([
    {
        $project: { 
            first_DBref_as_array: {$arrayElemAt: [{$objectToArray:{$arrayElemAt:["$bid",0]}},1]},
            second_DBref_as_array: {$arrayElemAt: [{$objectToArray:{$arrayElemAt:["$bid",0]}},1]},
            }

    },
    {
        $project: {
            first_DBref_as_ObjectId: "$first_DBref_as_array.v",
            second_DBref_as_ObjectId: "$second_DBref_as_array.v"
        }
    }

])

结果:

{
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
    "first_DBref_as_ObjectId" : ObjectId("582abcd85d2dfa67f44127e0"),
    "second_DBref_as_ObjectId" : ObjectId("582abcd85d2dfa67f44127e0")
}

很显然，在普通管道中，您不需要所有这些多余的步骤，使用嵌套的$ map，您可以一次性获得相同的结果:

Obviously, in a normal pipeline, you don't need all these redundant steps, using a nested $map, you can get to the same result in one go :

db.A.aggregate([
    {
        $project: { 
            B_fk: { $map : {input: { $map: {    input:"$bid",
                                    in: { $arrayElemAt: [{$objectToArray: "$$this"}, 1 ]}, } },
                            in: "$$this.v"}},

            }
    }, 

])

结果:

{
    "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
    "B_fk" : [
        ObjectId("582abcd85d2dfa67f44127e0"),
        ObjectId("582abcd85d2dfa67f44127e1")
    ]
}

我希望解释足够清楚，即使不能随便问.

I hope the explanation is clear enough, if not feel free to ask.

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