Mongodb Lookup query on collections with ObjectId as local and foreign fields?

Question

I have two collections on which i have to do lookup query on ObjectId of both collections. Both local and foreign fields are of type ObjectId. I can convert one collection ObjectId to string with $toString method but how will i do the same with the other collection i want to join?

Json 1:

{
    "_id": {
        "$oid": "4efcggedwrg446"
    },
    "name": "Name1",
    "phone": "12345678"
}

Json 2:

{
  "_id": {
    "$oid": "4efcggedwrg446"
  },
  "deviceId": "6552gggh732",
  "deviceName": "samsung"
}

Query:

[{$addFields: {
  "Id": { "$toString": "$_id" }
}}, {$lookup: {
  from: 'json2',
  localField: 'Id',
  foreignField: '_id',
  as: 'join'
}}]

Lookup query on _id of both json. How can i convert ObjectId of other collection ?

@NikhilSavaliya Is there any way i can do lookup operation of ObjectId ? then i don't need to convert it to string — Rahul Anand
yes you can do but your question seems odd, can you clarify your data you have provided — Nikhil Savaliya
I have two collections which are linked via ObjectId i.e "_id" . I need to join them via lookup to get device of each user. — Rahul Anand

Nikhil Savaliya Nikhil Savaliya · Accepted Answer · 2020-01-31T05:54:22

Collection1

{
    "_id" : ObjectId("5e33bfc008591b180967753a"),
    "name" : "Name1",
    "phone" : "12345678"
}

Collection2

{
    "_id" : ObjectId("5e33bfe508591b180967753b"),
    "iUserId" : ObjectId("5e33bfc008591b180967753a"),
    "deviceId" : "6552gggh732",
    "deviceName" : "samsung"
},

To join both with lookup

const query = [
    {
        $lookup:
            {
                from: "Collection2",
                localField: "_id",
                foreignField: "iUserId",
                as: "User"
            }
    },
    {
        $unwind: "$User"
    }
];

db.Collection1.aggregate(query)

Result:- You can apply $project to get specific fields

{
    "_id" : ObjectId("5e33bfc008591b180967753a"),
    "name" : "Name1",
    "phone" : "12345678",
    "User" : {
        "_id" : ObjectId("5e33bfe508591b180967753b"),
        "iUserId" : ObjectId("5e33bfc008591b180967753a"),
        "deviceId" : "6552gggh732",
        "deviceName" : "samsung"
    }
}

Mongodb Lookup query on collections with ObjectId as local and foreign fields?

1 Answers