如何在MongoDB中获取父子查询数据

Question

我正在构建一个简单的消息传递系统，我有一个messageSchema，设置如下：

const messageSchema = new mongoose.Schema({
replyTo: {
type: mongoose.Schema.ObjectId,
ref: 'Message',
default: null,
},
name: {
type: Object,
required: [true, 'Message has a name'],
},
user: {
type: mongoose.Schema.ObjectId,
ref: 'User',
},
email: {
type: Object,
required: [true, 'Message has a email'],
},
subject: {
type: String,
required: [true, 'Message has a subject'],
},
content: {
type: String,
required: [true, 'Message has content'],
},
createdAt: {
type: Date,
default: Date.now(),
},
status: {
type: String,
default: 'unread',
required: [true, 'Message has a status'],
},});

以下示例数据：

[
{
_id: ObjectId('53ed7efca75ca1a5248a281a'),
name: 'Person 1',
createdAt: ISODate('2021-01-01T01:00:00.000Z'),
subject: 'M1',
content: 'M1 content',
replyTo: null,
},
{
_id: ObjectId('53ed80bba75ca1a5248a281b'),
name: 'Person 2',
subject: 'M2 - Reply 1 to M1',
content: 'M2 content',
createdAt: ISODate('2021-01-01T02:00:00.000Z'),
replyTo: ObjectId('53ed7efca75ca1a5248a281a'),
},
{
_id: ObjectId('53ed80bba75ca1a5248a281c'),
name: 'Person 3',
subject: 'M3 - Reply 2 to M1',
content: 'M3 content',
createdAt: ISODate('2021-01-01T03:00:00.000Z'),
replyTo: ObjectId('53ed7efca75ca1a5248a281a'),
},
{
_id: ObjectId('53ed80bba75ca1a5248a281d'),
name: 'Person 4',
subject: 'M4',
content: 'M4 content',
createdAt: ISODate('2021-01-01T02:30:00.000Z'),
replyTo: null,
},
];

我现在正试图查询上面的示例以生成收件箱样式的响应，因此根消息(无replyTo(是顶级消息，具有包含最新消息信息的最新节点，并且在子节点中具有子节点(如果有的话(。有关所需输出，请参见下文。

[
{
_id: ObjectId('53ed7efca75ca1a5248a281a'),
name: 'Person 1',
createdAt: ISODate('2021-01-01T01:00:00.000Z'),
subject: 'M1',
content: 'M1 content',
replyTo: null,
latest: {
_id: ObjectId('53ed80bba75ca1a5248a281c'),
name: 'Person 3',
subject: 'M3 - Reply 2 to M1',
content: 'M3 content',
createdAt: ISODate('2021-01-01T03:00:00.000Z'),
replyTo: ObjectId('53ed7efca75ca1a5248a281a'),
},
children: [
{
_id: ObjectId('53ed80bba75ca1a5248a281b'),
name: 'Person 2',
subject: 'M2 - Reply 1 to M1',
content: 'M2 content',
createdAt: ISODate('2021-01-01T02:00:00.000Z'),
replyTo: ObjectId('53ed7efca75ca1a5248a281a'),
},
{
_id: ObjectId('53ed80bba75ca1a5248a281c'),
name: 'Person 3',
subject: 'M3 - Reply 2 to M1',
content: 'M3 content',
createdAt: ISODate('2021-01-01T03:00:00.000Z'),
replyTo: ObjectId('53ed7efca75ca1a5248a281a'),
},
]
},
{
_id: ObjectId('53ed80bba75ca1a5248a281d'),
name: 'Person 4',
subject: 'M4',
content: 'M4 content',
createdAt: ISODate('2021-01-01T02:30:00.000Z'),
replyTo: null,
latest: {
_id: ObjectId('53ed80bba75ca1a5248a281d'),
name: 'Person 4',
subject: 'M4',
content: 'M4 content',
createdAt: ISODate('2021-01-01T02:30:00.000Z'),
replyTo: null,
},
children: []
},];

感谢在整理此查询方面提供的任何帮助。谢谢

Answer 1

您可以使用聚合管道阶段，

• $matchreplyTous null条件
• $graphLookup加入同一集合以在children中获取回复消息
• $addFields检查条件为children为空，然后返回根文档，否则返回子级的最后一个元素

db.collection.aggregate([
{ $match: { replyTo: null } },
{
"$graphLookup": {
"from": "collection",
"startWith": "$_id",
"connectFromField": "_id",
"connectToField": "replyTo",
"as": "children"
}
},
{
$addFields: {
latest: {
$cond: [
{ $eq: [{ $size: "$children" }, 0] },
"$$ROOT",
{ $arrayElemAt: [{$slice: ["$children", -1]}, 0] }
]
}
}
}
])

游乐场

对于查询优化，您可以在客户端执行最后阶段的$addFields过程。