我有两个数据表,一个ugc_comment表,一个user表,现在想查询带回复的评论列表,列表中有用户信息,通过lookup查询,可以获取到主评论相关的回复列表和用户信息,但回复列表(replyList)中的用户信息不知道怎么查询才能获取到。有大神能帮忙看下吗?
查询结果如下:
[
{
"_id":"aefa8b0064f190db009c3be729a242b1",
"ugcId":"e8d27cb364ec5f2b0000a0f22c201605",
"content":"111",
"pid":null,
"gid":null,
"publishTime":1693552859878,
"userInfo":{
"openid":"xxx",
"nickName":"xxx",
"avatarUrl":"xxx"
},
"floor":1,
"replyList":[
]
},
{
"_id":"e8d27cb364f1b44800a2eb2c15ab0357",
"ugcId":"e8d27cb364ec5f2b0000a0f22c201605",
"content":"222",
"pid":null,
"gid":null,
"publishTime":1693561928633,
"floor":2,
"replyList":[
{
"_id":"aefa8b0064f1c4e900a2c2e252fcdc34",
"ugcId":"e8d27cb364ec5f2b0000a0f22c201605",
"content":"reply 222",
"pid":"e8d27cb364f1b44800a2eb2c15ab0357",
"gid":"e8d27cb364f1b44800a2eb2c15ab0357",
"publishTime":1693566185211,
"openId":"xxx",
"floor":2
},
{
"_id":"6cc20b0164f1e86f00a963156a319cb4",
"ugcId":"e8d27cb364ec5f2b0000a0f22c201605",
"content":"222-333",
"pid":"e8d27cb364f1b44800a2eb2c15ab0357",
"gid":"e8d27cb364f1b44800a2eb2c15ab0357",
"publishTime":1693575279559,
"openId":"xxx",
"floor":2
}
],
"userInfo":{
"openid":"xxx",
"nickName":"xxx",
"avatarUrl":"xxx"
}
},
{
"_id":"885b678664f1b4d4001898b536c4a7aa",
"ugcId":"e8d27cb364ec5f2b0000a0f22c201605",
"content":"333",
"pid":null,
"gid":null,
"publishTime":1693562068886,
"floor":3,
"replyList":[
],
"userInfo":{
"openid":"xxx",
"nickName":"xxx",
"avatarUrl":"xxx"
}
}
]
查询代码如下:
db.collection('ugc_comment').aggregate()
.match({
// ugcId为文章id
ugcId: "xxx",
// pid为评论的父id
pid: null
})
.sort({
// 评论发布时间升序
publishTime: 1,
})
.skip(0)
.limit(20)
// 本来想新建评论回复表来获取回复列表的,但发现这么写也可以获取到...
.lookup({
from: 'ugc_comment',
localField: '_id',
foreignField: 'gid',
as: 'replyList'
})
// 从user表查询评论关联的用户信息
.lookup({
from: 'user',
localField: 'openId',
foreignField: 'openid',
as: 'userInfo'
}).unwind('$userInfo')
// 去掉不需要的字段
.project({
"userInfo._id": 0,
"userInfo.gender":0,
"userInfo.homeCodes":0,
"userInfo.homePageImg":0,
"userInfo.identityType":0,
"userInfo.phoneNumber":0,
"userInfo.intro":0,
"userInfo.unionid":0,
openId: 0,
})
.end();
可以嵌套lookup,我很早前有写过,可以参考一下