Mongodb$lookup以检索作为子文档数组的菜单和子菜单

Question

看起来像是一个老问题，但我认为模式略有不同，我不确定这是否是正确的模式定义，但我希望这样

import { Schema, model } from 'mongoose';
import * as mongoose from 'mongoose';
const ObjectId = mongoose.Schema.Types.ObjectId;
const MenuSchema = new Schema({
parent: { 
type: Schema.Types.ObjectId,
ref: 'Menu', required: true, default: null },
url: {
type: String,
required: true,
unique: true,
},
title: {
en: {
type: String,
required: true,
},
ar: {
type: String,
required: true,
},
},
type: {
// internal or external
type: String,
required: false,
default: 'internal',
},
displayOrder: { type: Number, required: true, default: 0 },
image: {
// For icon
type: Object,
},
createdAt: { type: Date, default: Date.now },
createdBy: { type: ObjectId, ref: 'User' },
updatedAt: { type: Date, default: Date.now },
updatedBy: { type: ObjectId, ref: 'User' },
});

我的数据如下

{ 
"_id" : ObjectId("5f97be37bf401d75ed7de8a8"), 
"parent" : null, 
"displayOrder" : NumberInt(0), 
"image" : null, 
"title" : {
"en" : "Home", 
"ar" : "الصفحة الرئيسية"
}, 
"url" : "/"
}
{ 
"_id" : ObjectId("5f97eca04b076b2b8cb5646b"), 
"type" : "internal", 
"url" : "/about", 
"title" : {
"en" : "About", 
"ar" : "حول"
}, 
"createdAt" : ISODate("2020-10-27T09:47:12.396+0000"), 
"updatedAt" : ISODate("2020-10-27T09:47:12.396+0000"), 
"__v" : NumberInt(0), 
"displayOrder" : NumberInt(1), 
"image" : null, 
"parent" : null
}
{ 
"_id" : ObjectId("5f99492da0c7207dfb13aa03"), 
"type" : "internal", 
"url" : "/our-partners", 
"title" : {
"en" : "Our Partners", 
"ar" : "شركاؤنا"
}, 
"createdAt" : ISODate("2020-10-28T10:34:21.353+0000"), 
"updatedAt" : ISODate("2020-10-28T10:34:21.354+0000"), 
"__v" : NumberInt(0), 
"image" : null, 
"displayOrder" : NumberInt(8), 
"parent" : null
}
{ 
"_id" : ObjectId("5f9a7971f8443ecbdb787c4a"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(2), 
"url" : "/services", 
"title" : {
"en" : "Services", 
"ar" : "خدمات"
}, 
"createdAt" : ISODate("2020-10-29T08:12:33.366+0000"), 
"updatedAt" : ISODate("2020-10-29T08:12:33.366+0000"), 
"__v" : NumberInt(0), 
"image" : null
}
{ 
"_id" : ObjectId("5f9a93f3dc3f16e672d9e84e"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(3), 
"url" : "products", 
"title" : {
"en" : "Products", 
"ar" : "منتجات"
}, 
"createdAt" : ISODate("2020-10-29T10:05:39.551+0000"), 
"updatedAt" : ISODate("2020-10-29T10:05:39.551+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b7a9c95213c7e3736e63e"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(4), 
"url" : "projects", 
"title" : {
"en" : "Projects", 
"ar" : "المشاريع"
}, 
"createdAt" : ISODate("2020-10-30T02:29:48.863+0000"), 
"updatedAt" : ISODate("2020-10-30T02:29:48.863+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b7aa395213c7e3736e63f"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(5), 
"url" : "gallery", 
"title" : {
"en" : "Gallery", 
"ar" : "صالة عرض"
}, 
"createdAt" : ISODate("2020-10-30T02:29:55.845+0000"), 
"updatedAt" : ISODate("2020-10-30T02:29:55.845+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b7af995213c7e3736e640"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(6), 
"url" : "downloads", 
"title" : {
"en" : "Downloads", 
"ar" : "التحميلات"
}, 
"createdAt" : ISODate("2020-10-30T02:31:21.205+0000"), 
"updatedAt" : ISODate("2020-10-30T02:31:21.205+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b7b6095213c7e3736e641"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(7), 
"url" : "contact", 
"title" : {
"en" : "Contact", 
"ar" : "اتصل"
}, 
"createdAt" : ISODate("2020-10-30T02:33:04.071+0000"), 
"updatedAt" : ISODate("2020-10-30T02:33:04.071+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b8459391ab38846326a43"), 
"parent" : "5f97eca04b076b2b8cb5646b", 
"type" : "internal", 
"displayOrder" : NumberInt(0), 
"url" : "about", 
"title" : {
"en" : "Corporate Overview", 
"ar" : "لمحة عن الشركة"
}, 
"createdAt" : ISODate("2020-10-30T03:11:21.048+0000"), 
"updatedAt" : ISODate("2020-10-30T03:11:21.048+0000"), 
"__v" : NumberInt(0), 
"image" : null
}
{ 
"_id" : ObjectId("5f9b8bb8391ab38846326a44"), 
"parent" : "5f97eca04b076b2b8cb5646b", 
"type" : "internal", 
"displayOrder" : NumberInt(1), 
"url" : "md-message", 
"title" : {
"en" : "Md's Message", 
"ar" : "رسالة د"
}, 
"createdAt" : ISODate("2020-10-30T03:42:48.341+0000"), 
"updatedAt" : ISODate("2020-10-30T03:42:48.341+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b8c0e391ab38846326a45"), 
"parent" : "5f97eca04b076b2b8cb5646b", 
"type" : "internal", 
"displayOrder" : NumberInt(2), 
"url" : "certificates", 
"title" : {
"en" : "Certificates", 
"ar" : "الشهادات"
}, 
"createdAt" : ISODate("2020-10-30T03:44:14.876+0000"), 
"updatedAt" : ISODate("2020-10-30T03:44:14.876+0000"), 
"__v" : NumberInt(0), 
"image" : null
}
{ 
"_id" : ObjectId("5f9b8c4e391ab38846326a46"), 
"parent" : "5f9a7971f8443ecbdb787c4a", 
"type" : "internal", 
"displayOrder" : NumberInt(0), 
"url" : "before-sale-service", 
"title" : {
"en" : "Before Sale Service", 
"ar" : "خدمة ما قبل البيع"
}, 
"createdAt" : ISODate("2020-10-30T03:45:18.489+0000"), 
"updatedAt" : ISODate("2020-10-30T03:45:18.489+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b8c98391ab38846326a47"), 
"parent" : "5f9a7971f8443ecbdb787c4a", 
"type" : "internal", 
"displayOrder" : NumberInt(1), 
"url" : "installation", 
"title" : {
"en" : "Installation", 
"ar" : "التركيب"
}, 
"createdAt" : ISODate("2020-10-30T03:46:32.943+0000"), 
"updatedAt" : ISODate("2020-10-30T03:46:32.943+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b8ccb391ab38846326a48"), 
"parent" : "5f9a7971f8443ecbdb787c4a", 
"type" : "internal", 
"displayOrder" : NumberInt(3), 
"url" : "maintenance", 
"title" : {
"en" : "Maintenance", 
"ar" : "اعمال صيانة"
}, 
"createdAt" : ISODate("2020-10-30T03:47:23.943+0000"), 
"updatedAt" : ISODate("2020-10-30T03:47:23.944+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b8d04391ab38846326a49"), 
"parent" : "5f9a7971f8443ecbdb787c4a", 
"type" : "internal", 
"displayOrder" : NumberInt(3), 
"url" : "repairs-modernization", 
"title" : {
"en" : "REPAIRS AND MODERNIZATION", 
"ar" : "الإصلاح والتحديث"
}, 
"createdAt" : ISODate("2020-10-30T03:48:20.958+0000"), 
"updatedAt" : ISODate("2020-10-30T03:48:20.958+0000"), 
"__v" : NumberInt(0), 
"image" : null
}
{ 
"_id" : ObjectId("5f9b90180d34dd99a22729fc"), 
"parent" : "5f9b7aa395213c7e3736e63f", 
"type" : "internal", 
"displayOrder" : NumberInt(0), 
"url" : "image-gallery", 
"title" : {
"en" : "Image Gallery", 
"ar" : "معرض الصور"
}, 
"createdAt" : ISODate("2020-10-30T04:01:28.652+0000"), 
"updatedAt" : ISODate("2020-10-30T04:01:28.652+0000"), 
"__v" : NumberInt(0)
}
{ 
"_id" : ObjectId("5f9b903a0d34dd99a22729fd"), 
"parent" : "5f9b7aa395213c7e3736e63f", 
"type" : "internal", 
"displayOrder" : NumberInt(1), 
"url" : "video-gallery", 
"title" : {
"en" : "Video Gallery", 
"ar" : "معرض الفيديو"
}, 
"createdAt" : ISODate("2020-10-30T04:02:02.999+0000"), 
"updatedAt" : ISODate("2020-10-30T04:02:02.999+0000"), 
"__v" : NumberInt(0)
}

检索带有子菜单(parent:someid(的父菜单(parend:null(的"我的查询"位于以下

db.getCollection("menus").aggregate([
{
$match: {
'parent': null   
}
},
{
$lookup: {
from: 'menus',
localField: '_id',
foreignField: 'parent',
as: 'childs'
}
}
])

但是上面查询中的子文档数组为空

{ 
"_id" : ObjectId("5f97be37bf401d75ed7de8a8"), 
"parent" : null, 
"displayOrder" : NumberInt(0), 
"image" : null, 
"title" : {
"en" : "Home", 
"ar" : "الصفحة الرئيسية"
}, 
"url" : "/", 
"childs" : [
]
}
{ 
"_id" : ObjectId("5f97eca04b076b2b8cb5646b"), 
"type" : "internal", 
"url" : "/about", 
"title" : {
"en" : "About", 
"ar" : "حول"
}, 
"createdAt" : ISODate("2020-10-27T09:47:12.396+0000"), 
"updatedAt" : ISODate("2020-10-27T09:47:12.396+0000"), 
"__v" : NumberInt(0), 
"displayOrder" : NumberInt(1), 
"image" : null, 
"parent" : null, 
"childs" : [
]
}
{ 
"_id" : ObjectId("5f99492da0c7207dfb13aa03"), 
"type" : "internal", 
"url" : "/our-partners", 
"title" : {
"en" : "Our Partners", 
"ar" : "شركاؤنا"
}, 
"createdAt" : ISODate("2020-10-28T10:34:21.353+0000"), 
"updatedAt" : ISODate("2020-10-28T10:34:21.354+0000"), 
"__v" : NumberInt(0), 
"image" : null, 
"displayOrder" : NumberInt(8), 
"parent" : null, 
"childs" : [
]
}
{ 
"_id" : ObjectId("5f9a7971f8443ecbdb787c4a"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(2), 
"url" : "/services", 
"title" : {
"en" : "Services", 
"ar" : "خدمات"
}, 
"createdAt" : ISODate("2020-10-29T08:12:33.366+0000"), 
"updatedAt" : ISODate("2020-10-29T08:12:33.366+0000"), 
"__v" : NumberInt(0), 
"image" : null, 
"childs" : [
]
}
{ 
"_id" : ObjectId("5f9a93f3dc3f16e672d9e84e"), 
"parent" : null, 
"type" : "internal", 
"displayOrder" : NumberInt(3), 
"url" : "products", 
"title" : {
"en" : "Products", 
"ar" : "منتجات"
}, 
"createdAt" : ISODate("2020-10-29T10:05:39.551+0000"), 
"updatedAt" : ISODate("2020-10-29T10:05:39.551+0000"), 
"__v" : NumberInt(0), 
"childs" : [
]
}

我希望将父列表和子菜单作为子文档来实现如下结果。当前，我的子数组为空，带有上述查询。看起来很简单，但我发现很难。请帮忙

[
{
"_id": "5f97be37bf401d75ed7de8a8",
"parent": null,
"displayOrder": 0,
"image": null,
"title": {
"en": "Home",
"ar": "الصفحة الرئيسية"
},
"url": "/",
"childs": []
},
{
"_id": "5f97eca04b076b2b8cb5646b",
"type": "internal",
"url": "/about",
"title": {
"en": "About",
"ar": "حول"
},
"createdAt": "2020-10-27T09:47:12.396Z",
"updatedAt": "2020-10-27T09:47:12.396Z",
"__v": 0,
"displayOrder": 1,
"image": null,
"parent": null,
"childs" : [
{ 
"_id" : "5f9b8459391ab38846326a43", 
"parent" : "5f97eca04b076b2b8cb5646b", 
"type" : "internal", 
"displayOrder" : 0, 
"url" : "about", 
"title" : {
"en" : "Corporate Overview", 
"ar" : "لمحة عن الشركة"
}, 
"createdAt" : ISODate("2020-10-30T03:11:21.048+0000"), 
"updatedAt" : ISODate("2020-10-30T03:11:21.048+0000"), 
"__v" : 0, 
"image" : null
},
{ 
"_id" : "5f9b8c0e391ab38846326a45", 
"parent" : "5f97eca04b076b2b8cb5646b", 
"type" : "internal", 
"displayOrder" : 1, 
"url" : "certificates", 
"title" : {
"en" : "Certificates", 
"ar" : "الشهادات"
}, 
"createdAt" : ISODate("2020-10-30T03:44:14.876+0000"), 
"updatedAt" : ISODate("2020-10-30T03:44:14.876+0000"), 
"__v" : 0, 
"image" : null
},
...  
]
},

]

Answer 1

模式中的parent字段被定义为type: Schema.Types.ObjectId,，但在数据库中，所有字段都是string。这是一个矛盾。

为了解决您的问题，您只需要将所有parent字段的类型从string替换为ObjectId，因为$lookup使用相等匹配，而string与ObjectId不相等。

因此，例如，您的一个文档应该是这样的：

{ 
"_id" : ObjectId("5f9b8bb8391ab38846326a44"),
"parent" : ObjectId("5f97eca04b076b2b8cb5646b"),
"type" : "internal",
"displayOrder" : NumberInt(1),
"url" : "md-message",
"title" : {
"en" : "Md's Message",
"ar" : "رسالة د"
},
"createdAt" : ISODate("2020-10-30T03:42:48.341+0000"),
"updatedAt" : ISODate("2020-10-30T03:42:48.341+0000"),
"__v" : NumberInt(0)
}

经过本地测试，可以确认这一点。