Mongo how to $lookup with DBRef

Question

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

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

and Collection B:

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

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.

Answer 1

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.

Solution

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

result

{     "_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")         }     ] } 

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.

Step-by-step Explanation

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

If you do:

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

This is the result:

{ "_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")     } ] } 

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

result:

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

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

result:

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

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

result:

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

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