users
{
"_id":"12345",
"admin":1
},
{
"_id":"123456789",
"admin":0
}
posts
{
"content":"Some content",
"owner_id":"12345",
"via":"facebook"
},
{
"content":"Some other content",
"owner_id":"123456789",
"via":"facebook"
}
Here is a sample from my mongodb. I want to get all the posts which has "via" attribute equal to "facebook" and posted by an admin ("admin":1). I couldn't figure out how to acquire this query. Since mongodb is not a relational database, I couldn't do a join operation. What could be the solution ?
This question is related to
collections
join
mongodb
You can use $lookup
( multiple ) to get the records from multiple collections:
Example:
If you have more collections ( I have 3 collections for demo here, you can have more than 3 ). and I want to get the data from 3 collections in single object:
The collection are as:
db.doc1.find().pretty();
{
"_id" : ObjectId("5901a4c63541b7d5d3293766"),
"firstName" : "shubham",
"lastName" : "verma"
}
db.doc2.find().pretty();
{
"_id" : ObjectId("5901a5f83541b7d5d3293768"),
"userId" : ObjectId("5901a4c63541b7d5d3293766"),
"address" : "Gurgaon",
"mob" : "9876543211"
}
db.doc3.find().pretty();
{
"_id" : ObjectId("5901b0f6d318b072ceea44fb"),
"userId" : ObjectId("5901a4c63541b7d5d3293766"),
"fbURLs" : "http://www.facebook.com",
"twitterURLs" : "http://www.twitter.com"
}
Now your query will be as below:
db.doc1.aggregate([
{ $match: { _id: ObjectId("5901a4c63541b7d5d3293766") } },
{
$lookup:
{
from: "doc2",
localField: "_id",
foreignField: "userId",
as: "address"
}
},
{
$unwind: "$address"
},
{
$project: {
__v: 0,
"address.__v": 0,
"address._id": 0,
"address.userId": 0,
"address.mob": 0
}
},
{
$lookup:
{
from: "doc3",
localField: "_id",
foreignField: "userId",
as: "social"
}
},
{
$unwind: "$social"
},
{
$project: {
__v: 0,
"social.__v": 0,
"social._id": 0,
"social.userId": 0
}
}
]).pretty();
Then Your result will be:
{
"_id" : ObjectId("5901a4c63541b7d5d3293766"),
"firstName" : "shubham",
"lastName" : "verma",
"address" : {
"address" : "Gurgaon"
},
"social" : {
"fbURLs" : "http://www.facebook.com",
"twitterURLs" : "http://www.twitter.com"
}
}
If you want all records from each collections then you should remove below line from query:
{
$project: {
__v: 0,
"address.__v": 0,
"address._id": 0,
"address.userId": 0,
"address.mob": 0
}
}
{
$project: {
"social.__v": 0,
"social._id": 0,
"social.userId": 0
}
}
After removing above code you will get total record as:
{
"_id" : ObjectId("5901a4c63541b7d5d3293766"),
"firstName" : "shubham",
"lastName" : "verma",
"address" : {
"_id" : ObjectId("5901a5f83541b7d5d3293768"),
"userId" : ObjectId("5901a4c63541b7d5d3293766"),
"address" : "Gurgaon",
"mob" : "9876543211"
},
"social" : {
"_id" : ObjectId("5901b0f6d318b072ceea44fb"),
"userId" : ObjectId("5901a4c63541b7d5d3293766"),
"fbURLs" : "http://www.facebook.com",
"twitterURLs" : "http://www.twitter.com"
}
}
As mentioned before in MongoDB you can't JOIN between collections.
For your example a solution could be:
var myCursor = db.users.find({admin:1});
var user_id = myCursor.hasNext() ? myCursor.next() : null;
db.posts.find({owner_id : user_id._id});
See the reference manual - cursors section: http://es.docs.mongodb.org/manual/core/cursors/
Other solution would be to embed users in posts collection, but I think for most web applications users collection need to be independent for security reasons. Users collection might have Roles, permissons, etc.
posts
{
"content":"Some content",
"user":{"_id":"12345", "admin":1},
"via":"facebook"
},
{
"content":"Some other content",
"user":{"_id":"123456789", "admin":0},
"via":"facebook"
}
and then:
db.posts.find({user.admin: 1 });
Here is answer for your question.
db.getCollection('users').aggregate([
{$match : {admin : 1}},
{$lookup: {from: "posts",localField: "_id",foreignField: "owner_id",as: "posts"}},
{$project : {
posts : { $filter : {input : "$posts" , as : "post", cond : { $eq : ['$$post.via' , 'facebook'] } } },
admin : 1
}}
])
Or either you can go with mongodb group option.
db.getCollection('users').aggregate([
{$match : {admin : 1}},
{$lookup: {from: "posts",localField: "_id",foreignField: "owner_id",as: "posts"}},
{$unwind : "$posts"},
{$match : {"posts.via":"facebook"}},
{ $group : {
_id : "$_id",
posts : {$push : "$posts"}
}}
])
Perform multiple queries or use embedded documents or look at "database references".
One solution: add isAdmin: 0/1 flag to your post collection document.
Other solution: use DBrefs
Source: Stackoverflow.com