对您的问题使用聚合查询,我已经为您创建了一个查询。
users.collection.json
/* 1 */
{
"_id" : ObjectId("61056c4a8cca27df3db2e4c8"),"firstName" : "Rahul","lastName" : "soni","fleet" : [
"FleetA","FleetB","FleetC"
],"createdAt" : ISODate("2021-07-31T15:29:14.918Z")
}
userqualifications.collection.json
/* 1 */
{
"_id" : ObjectId("61056c908cca27df3db2e4c9"),"user" : ObjectId("61056c4a8cca27df3db2e4c8"),"createdAt" : ISODate("2021-07-31T15:30:24.510Z")
}
聚合查询:
只有当用户拥有FleetA 和FleetC 时,它才会得到结果。
如果有人不匹配,则返回 0 条记录
db.userqualifications.aggregate([{
"$lookup": {
"from": "users","localField": "user","foreignField": "_id","as": "user"
}
},{
"$unwind": "$user"
},{
"$match": {
"user.fleet": {
"$elemMatch": {
"$eq": "FleetA","$eq": "FleetC"
}
}
}
}])
结果:
/* 1 */
{
"_id" : ObjectId("61056c908cca27df3db2e4c9"),"user" : {
"_id" : ObjectId("61056c4a8cca27df3db2e4c8"),"fleet" : [
"FleetA","FleetC"
],"createdAt" : ISODate("2021-07-31T15:29:14.918Z")
},"createdAt" : ISODate("2021-07-31T15:30:24.510Z")
}
,
如果目标是仅获取 UserQualifications
,那么以下应该是一种有效的方法,因为它可以使用 fleet
集合的 User
字段上的索引。
db.User.aggregate([
{
$match: {
fleet: { $in: ["FleetA","FleetB"] }
}
},{
$lookup: {
from: "UserQualification",localField: "_id",foreignField: "user",as: "qualifications"
}
},{
$unwind: "$qualifications"
},{
$replaceWith: "$qualifications"
}
])
另一方面,如果您从 UserQualifications
集合开始,您无法有效地缩小记录范围,因为您正在过滤没有数据的内容。
,
感谢您的回答 - 它确实达到了我想要的结果 - 但是我现在正在努力将带有 $match
的 $and
添加到聚合中,以仅返回用户 ID 所在的资格在提交的数组和给定的队列中等于 1。
我有以下聚合:
db.UserQualifications.aggregate([{
{
$lookup: {
from: 'users',localField: 'user',foreignField: '_id',as: 'user',},{
$unwind: '$user',{
$match: {
$and: [
'user.fleet': {
$in: ["Fleet A","Fleet C"],// This works on it's own
},user: { // Also tried 'user._id'
$in: ["6033e4129070031c07fbbf29"] // Adding this returns blank array
}
]
},}
}])
我已经仔细检查过我在数组内传递了正确的用户 ID,但是当我将它添加到 $and
内部匹配时,它只返回一个空白数组。
还有其他方法可以做到这一点吗?
,
// Updated users collection
/* 1 */
{
"_id" : ObjectId("61056c4a8cca27df3db2e4c8"),"fleet" : [
"Fleet A","Fleet B","Fleet C"
],"createdAt" : ISODate("2021-07-31T15:29:14.918Z")
}
查询:
// userqualifications => this is my collection name,you can add your collection name here db.<YOUR>
db.userqualifications.aggregate([{
$lookup: {
from: 'users',{
$unwind: '$user',{
$match: {
// $and operatory syntax [{},{}]
$and: [{
'user.fleet': {
// "Fleet A","Fleet C" (FleetA,FleetC) this is the my first options,// I have changes according to your problem
$in: ["Fleet A",// This works on it's own
}
},{
// Convert user id to ObjectId type (_bsonType)
"user._id": ObjectId("61056c4a8cca27df3db2e4c8")
}]
}
}
])
结果:
/* 1 */
{
"_id" : ObjectId("61056c908cca27df3db2e4c9"),"fleet" : [
"Fleet A","Fleet C"
],"createdAt" : ISODate("2021-07-31T15:30:24.510Z")
}
差异图像:
本文链接:https://www.f2er.com/5073.html