how to join 4 mongo collections

Question

I am using python flask and I want to join 4 collections. I can either do it on server or using python code. Whichever is easier to achieve this. I want to get all data for all ids present in the collection A. For e.g.: The rest I’m looking for is an object within b_id is the data which matches the data, same with C and D. How can I achieve this?

Collection A:

{ 
    “b_id" : "532a234234….”,
    “c_id" : “532fdf….”,
    “d_id”:"532fdf…."

}

Collection B:
{ 
    "_id" : ObjectId(“532a234234…."), 
    “b_type" : “mobile", 
    “b_desc" : “more data….data..."
}



Collection C:
{ 
    "_id" : ObjectId(“532fdf…."), 
    “c_name" : “more data….data..."
}



Collection D:
{ 
    "_id" : ObjectId(“532fdf…."), 
    “d_value" : “more data….data..."
}

I tired MongoJoin in python but I'm getting process module not installed as an error :(

Nozar Safari · Accepted Answer · 2019-01-14T09:24:21.413

4

Mongo 4 Realese (Edit)

you can use $addfield and $toObjectId and lookup on that newfield

first your A collection have a proplem , change it to

{ 

    “b_id" : ObjectId("532a234234….”),
    “c_id" : ObjectId(“532fdf….”),
    “d_id”: ObjectId("532fdf….")
}

for test u to see result

u can use Robo3t to see colection information (not necessary but help a lot )

in mongoShell u can join this 4 collection like this

db.A.aggregate([
{"$lookup" :
    {
    "from" : "B",
    "localField" : "b_id",
    "foreignField" : "_id",
    "as" : "b_info"
    }
},
{"$unwind" :"$b_info"}, // if u want can ignore this line
{"$lookup" :
    {
    "from" : "C",
    "localField" : "c_id",
    "foreignField" : "_id",
    "as" : "c_info"
    }
},
{"$unwind" :"$c_info"}, // if u want can ignore this line
{"$lookup" :
    {
    "from" : "D",
    "localField" : "d_id",
    "foreignField" : "_id",
    "as" : "d_info"
    }
},
{"$unwind" :"$d_info"}, // if u want can ignore this line

])

now for python u can exatcly run mongo command with pymongo first install it with pip then in your code

from pymongo import MongoClient
client = MongoClient('localhost', 27017) # change Address and port if not use local host
db = client.DBNAME # change DBNAME to your DBNAME
result = list(db.A.aggregate([
{"$lookup" :
    {
    "from" : "B",
    "localField" : "b_id",
    "foreignField" : "_id",
    "as" : "b_info"
    }
},
{"$unwind" :"$b_info"}, # if u want can ignore this line
{"$lookup" :
    {
    "from" : "C",
    "localField" : "c_id",
    "foreignField" : "_id",
    "as" : "c_info"
    }
},
{"$unwind" :"$c_info"}, # if u want can ignore this line
{"$lookup" :
    {
    "from" : "D",
    "localField" : "d_id",
    "foreignField" : "_id",
    "as" : "d_info"
    }
},
{"$unwind" :"$d_info"}, # if u want can ignore this line
]))

Edit (convert string to ObjectId)

first solution is before create Json create their ObjectID (if you have paralel or async there is very very very low chance too create 2 same ObjectID and faild to insert as you know value object id generated by time stamp)

from bson import objectid
b_id = objectid.ObjectId()
c_id =  objectid.ObjectId()
d_id =  objectid.ObjectId()
objB["_id"] =b_id
objC["_id"] = c_id
objD["_id] = d_id
objA["b_id"] = b_id
objA["c_id"] = c_id
objA["d_id"] = d_id
# insert 4 into DB here

second way u already create A just need to convert string into ObjectID:

from bson import objectid
# objA it what u want to insert in A collection
for key in objA:
    objA[key] = objectid.ObjectId(objA[key])
db.A.save(objA)

edited Jan 14 '19 at 09:24

answered Dec 16 '17 at 08:41

Nozar Safari

505
4
17

Thank You Nozar. How can I match an ObjectId with a String while creating an aggregate? – jason Dec 16 '17 at 09:09
I get an error on the editor in python when I use the python code at the lookup part – jason Dec 16 '17 at 09:19
sry my mistake i will edit it . first delet `self` in client second type `$lookup` and `$unwind` in " (Javascript work with out " )(Fixed) – Nozar Safari Dec 16 '17 at 09:30
ObjectId is not String u must `from bson import objectid` – Nozar Safari Dec 16 '17 at 09:32
Is there a way to typecast a variable in python-mongo. For example: If one field is in Int and other field is in ObjectId ? PLease answer this. My A.id is string while B.id is ObjectId. If I run the above query it does not work – jason Dec 16 '17 at 09:46
can u explain or give a exapmle ? i dont understand – Nozar Safari Dec 16 '17 at 09:52
Ok. In **Collection A** : **b_id** is a **String** but in **Collection B** : **_id** is **ObjectId** so when you try to aggregate the two it wont work as it's of different datatype. Is there a way to convert b_id to ObjectId with an aggregate? – jason Dec 16 '17 at 09:57
mongo cant do it in aggregation (https://stackoverflow.com/questions/41042482/change-type-of-field-inside-mongodb-aggregation-and-does-lookup-utilises-index) – Nozar Safari Dec 16 '17 at 10:04
Something like this : **user_bookmarked.brands.map((brand) => return mongoose.Types.ObjectId(brand) )** in python – jason Dec 16 '17 at 10:36
pymongo only use **pure** mongo commands , mongoose is not pure mongo, as i know its from npm and for nodeJS – Nozar Safari Dec 16 '17 at 10:50
Is there something like mongoose on python which can do the same? – jason Dec 16 '17 at 10:58
i had same problem and search a lot and didn't find anything, So change my collections and save exact value of **foreign key** in another collection – Nozar Safari Dec 16 '17 at 11:05
So you are creating all ids as ObjectId For eg: **b_id,c_id** as ObjectId? – jason Dec 16 '17 at 11:10
If I'm sending a nested json data via python to mongodb then how can I replace the json code from string to objectId? Please can you share the code? Thank You, Nozar :) – jason Dec 16 '17 at 17:50
there is very rare problem wher `pip` **pymongo** and **bson** they somthing like override each other u must `unistal` and in `install` module again in this case – Nozar Safari Dec 17 '17 at 06:00

how to join 4 mongo collections

1 Answers1