Mongo Query: how to $lookup with DBRef

Question

I have a trouble with $lookup with DBRef. I couldn't find the solution for below scenario anywhere. Someone please help me here?

Suppose the Collection A is

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

and Collection B:

{ 
"_id" : ObjectId("582abcd85d2dfa67f44127e1"),
"Name" : "from B Collection"
"bid" : DBRef("B", ObjectId("582abcd85d2dfa67f44127e0"))   }

I have spent lot of time in aggregating above two collections. I am looking for the output as below.

{ 
"_id" : ObjectId("582abcd85d2dfa67f44127e0"),  
"status" : NumberInt(1), 
"seq" : NumberInt(0),
B: [
    {
        "_id" : ObjectId("582abcd85d2dfa67f44127e1"),
        "Name" : "from B Collection"
    }
]}

Please help me with the Mongo query to retrieve the result in the above format. Thanks in advance

do you have to store the data as a db ref here `"bid" : DBRef("B", ObjectId("582abcd85d2dfa67f44127e0"))`? or would you be able to store it as something like `"bid" : ObjectId("582abcd85d2dfa67f44127e0")` ? — Katherine R, May 15 '19 at 23:41
We have vast majority of data with DBRef, so I have to find a query to bring in the collective output as mentioned above. I am not sure how much effort is required to convert DBRef to ObjectId for all our data(Terabytes). — SantoshVysyaraju, May 16 '19 at 13:36

score 9 · Accepted Answer · answered May 18 '19 at 01:24

Ideally you would be able to change the DBRef to a plain objectId or just string type. As noted in this post, it can be convoluted to use a DBRef in a lookup. The key is an $addFields stage with {$objectToArray: "$$ROOT.bid"} to get the DBRef value into a usable format.

You'll need to start the aggregation from collection B since that is where the reference is -- and that DBRef needs massaging before doing the lookup. Knowing that is the case, maybe the goal output shape might change; however, here is an aggregation that works to get you what you need:

db.getCollection('B').aggregate([
{$addFields: {fk: {$objectToArray: "$$ROOT.bid"}}},
{$lookup: {
    from: 'A',
    localField: 'fk.1.v',
    foreignField: '_id',
    as: 'A'
}},
// the below is transforming data into the format in the example
{$addFields: {'A.B': {_id: '$_id', Name: '$Name'}}},
{$unwind: '$A'},
{$replaceRoot: {newRoot: '$A'}}
])

You might need to do a groupBy if there are multiple B matches you need to group into an array.

Thank you very much Katherine for the solution. it is working. I would have mentioned my scenario better. I have total 5 collection which are referencing one collection using DBRef (i.e _id of Collection A is been foreign key for COllection B, C, D & E). Does this solution works if I extend it little bit? In output I need Collection A document along with Document B, C , D & E data with their labels. Sorry for not mentioning my exact requirement before. — SantoshVysyaraju, May 20 '19 at 17:34

Mongo Query: how to $lookup with DBRef

1 Answers1