I am trying to lookup child collection in mongodb using moongose in node.js but it will lookup all child records, i want to lookup only active records.
Parent Collection is Users Child Collection - Post which contains post published by user and have a user_id as refrence of Users collection. I tried below query but no luck
User Collection Sample :
{
"_id" : ObjectId("584016e28880811461000007"),
"fname" : "Test",
"lname" : "user",
"email" : "test@user.com",
},
{
"_id" : ,
"fname" : "Test2",
"lname" : "user2",
"email" : "test2@user.com",
}
Post Collection Sample :
{
"_id" : ObjectId("5863aed8cafb42674600000e"),
"user_id" : ObjectId("584016e28880811461000007"),
"title" : "Post 1",
"is_deleted" : false
},
{
"_id" : ,
"user_id" : ObjectId("584016e28880811461000007"),
"title" : "Post 2",
"is_deleted" : true
}
Query I tried :
Users.collection.aggregate([
{ $lookup: {from: "Posts", localField: "_id", foreignField: "user_id", as: "Posts"} },
{ $match : { $or : [{"Posts._id" : {$exists : true}, "Posts.is_deleted" : false}] }},
{$project: {fname: "$fname", lname : "$lname", email : "$email", "publishedPosts" : {$size : "$Posts"}}}
])
Result i got from above code :
[
{"fname" : "Test", "lname" : "user", "email" : "test@user.com", "publishedPosts" : 2},
{"fname" : "Test2", "lname" : "user2", "email" : "test2@user.com", "publishedPosts" : 0}
]
Result Expected :
[
{"fname" : "Test", "lname" : "user", "email" : "test@user.com", "publishedPosts" : 1},
{"fname" : "Test2", "lname" : "user2", "email" : "test2@user.com", "publishedPosts" : 0}
]
it lookup all post, whther is_deleted is false or true
Please help me with some similar query which will work for me
The $match stage you're using is wrong. The first condition ( {"Posts._id" : {$exists : true} ) is useless because each document has to have an _id field ( see mongodb _id field for details ).
Update: Do the $match stage before $lookup for better performances. Lookup from Posts to Users, then unwind Users and group documents for the sum
this query return the result you're expecting:
db.Posts.aggregate([
{
$match:{
is_deleted:false
}
},
{
$lookup:{
from:"Users",
localField:"user_id",
foreignField:"_id",
as:"Users"
}
},
{
$unwind:"$Users"
},
{
$group:{
_id:"$user_id",
lname:{
$first:"$Users.lname"
},
fname:{
$first:"$Users.fname"
},
email:{
$first:"$Users.email"
},
publishedPosts:{
$sum:1
}
}
}
])
output:
{ "_id" : ObjectId("584016e28880811461000007"), "lname" : "user", "fname" : "Test", "email" : "test@user.com", "publishedPosts" : 1 }
you can try this , it working
shop collection
{
name:"shop1",
status:true,
_id : ObjectId("583d0c1f2bd2cbdb117053c5")
}
{
name:"shop2",
status:false,
_id : ObjectId("583d0c1f2bd2cbdb117053c6")
}
order collection
{
orderNo:1,
price:100,
product:"product1",
shop:ObjectId("583d0c1f2bd2cbdb117053c5")
}
try this
db.getCollection('orders').aggregate([
{
$lookup:
{
from: "shop",
localField: "shop",
foreignField: "_id",
as: "shopData"
}
},
{ $match : { $or: [ { "shopData.status": true}] } }
])
result :
[{
orderNo:1,
price:100,
product:"product1",
shop:[{
name:"shop1",
status:true,
_id : ObjectId("583d0c1f2bd2cbdb117053c5")
}]
}]