1

[{
$lookup: {
from: ‘RoleEntity’,
let: {
anotherRoleId: “$roleId”
},
pipeline: [{
$match: {
“serverId”: 3,
“lastLoginTime”: {
$lt: 1588435200000
},
“createTime”: {
$gte: 0,
$lte: 1588435199999
}
}
},
{
$match: {
$expr: {
$eq: [“$roleId”, “$$anotherRoleId”]
}
}
},
],
as: ‘roles’
}
}, {
$match: {
“roles”: {
$gt: {
$size: 0
}
}
}
}, {
$group: {
_id: “$curChapterId”,
count: {
$sum: 1
}
}
}]

怎么在不使用let标签的情况下完成查询?

我期望的查询伪sql是:

SELECT curChapterId,COUNT(*) FROM RoleChapterEntity WHERE roleId in (SELECT roleId FROM RoleEntity WHERE …) GROUP BY curChapterId;

在RoleChapterEntity的roleId和curChapterId有索引

我写的mongo查询语句可以怎么优化吗?

问的问题