MongoDB 一次查询多个集合

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

这是我的样品。我想得到所有的文章，具有“ via”属性等于“ facebook”和管理员发布(“管理员”: 1)。我不知道如何获取这个查询。由于 mongodb 不是关系数据库，我无法进行联合操作。解决办法是什么？

222475 次浏览
小开

Perform multiple queries or use embedded documents or look at "database references".

小开
最佳答案

Trying to JOIN in MongoDB would defeat the purpose of using MongoDB. You could, however, use a DBref and write your application-level code (or library) so that it automatically fetches these references for you.

Or you could alter your schema and use embedded documents.

Your final choice is to leave things exactly the way they are now and do two queries.

小开

One solution: add isAdmin: 0/1 flag to your post collection document.

Other solution: use DBrefs

小开

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

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"
}
}
小开

Posting since I wanted to flatten the merged documents, vs a tiered document that the other answers produce.

To merge multiple collections into a flat single document, look at Mongo docs for $lookup with $mergeObjects: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/#use--lookup-with--mergeobjects


提交答案