gpt4 book ai didi

javascript - 来自多个集合的 $lookup 和嵌套输出

转载 作者:可可西里 更新时间:2023-11-01 10:40:22 25 4
gpt4 key购买 nike

我有多个集合,我使用了单独的集合和外键方法,我想加入这个集合来构建一个嵌套的集合。
这是我的收藏模式:

const SurveySchema = new Schema({
_id:{ type: Schema.ObjectId, auto: true },
name: String,
enabled: {type: Boolean, Default: true},
created_date:{type: Date, Default: Date.now},
company: {type: Schema.Types.ObjectId, ref: 'Company'},});
const GroupSchema = new Schema({
_id:{ type: Schema.ObjectId, auto: true },
name: String,
order: String,
created_date:{type: Date, Default: Date.now},
questions: [{type: Schema.Types.ObjectId, ref: 'Question'}],
survey: {type: Schema.Types.ObjectId, ref: 'Survey'}
});
const ResponseSchema = new Schema({
_id:{ type: Schema.ObjectId, auto: true },
response_text: String,
order: String,
created_date:{type: Date, Default: Date.now},
question:{type: Schema.Types.ObjectId, ref: 'Question'}
});

这是我构建这个嵌套对象的代码:
Survey.aggregate([
{ $match: {} },
{ $lookup: {
from: 'groups',
localField: '_id',
foreignField: 'survey',
as: 'groupsofquestions',
}},
{ $unwind: {
path: "$groupsofquestions",
preserveNullAndEmptyArrays: true
}},
{ $lookup: {
from: 'questions',
localField: 'groupsofquestions._id',
foreignField: 'group',
as: 'questionsofgroup',
}},
{ $lookup: {
from: 'response',
localField: 'questionsofgroup._id',
foreignField: 'question',
as: 'responses',
}},
{ $group: {
_id: "$_id",
name: {$first: "$name"},
groups: {$push: {
id: "$groupsofquestions._id",
name: "$groupsofquestions.name",
questions: "$questionsofgroup",
reponses: "$responses"
}}
}}
])

我想结构如下,(也有外部链接):

http://jsoneditoronline.org/?id=d7d1779b3b95e3acb28f8a2be0785423
[
{
"__v": 0,
"_id": "59b6715725dcd2060da7f591",
"company": "59b6715725dcd2060da7f58f",
"created_date": "2017-09-11T11:19:51.709Z",
"enabled": true,
"name": "function String() { [native code] }",
"groups": [
{
"_id": "59b6715725dcd2060da7f592",
"name": "groupe 1 des question",
"order": "1",
"created_date": "2017-09-11T11:19:51.709Z",
"survey": "59b6715725dcd2060da7f591",
"__v": 0,
"questions": [
{
"_id": "59b6715725dcd2060da7f594",
"question_text": "question 1 group 1",
"order": "1",
"created_date": "2017-09-11T11:19:51.709Z",
"group": "59b6715725dcd2060da7f592",
"__v": 0,
"responses": [
{
"_id": "59b6715725dcd2060da7f598",
"response_text": "reponse 1 question 1 group 1",
"order": "1",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f594",
"__v": 0
},
{
"_id": "59b6715725dcd2060da7f599",
"response_text": "reponse 2 question 1 group 1",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f594",
"__v": 0
}
]
},
{
"_id": "59b6715725dcd2060da7f595",
"question_text": "question 2 group 1",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"group": "59b6715725dcd2060da7f592",
"__v": 0,
"responses": [
{
"_id": "59b6715725dcd2060da7f59a",
"response_text": "reponse 1 question 2 group 1",
"order": "1",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f595",
"__v": 0
},
{
"_id": "59b6715725dcd2060da7f59b",
"response_text": "reponse 2 question 2 group 1",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f595",
"__v": 0
}
]
}
]
},
{
"_id": "59b6715725dcd2060da7f593",
"name": "groupe 2 des question",
"order": "2",
"created_date": "2017-09-11T11:19:51.709Z",
"survey": "59b6715725dcd2060da7f591",
"__v": 0,
"questions": [
{
"_id": "59b6715725dcd2060da7f596",
"question_text": "question 1 group 1",
"order": "1",
"created_date": "2017-09-11T11:19:51.710Z",
"group": "59b6715725dcd2060da7f592",
"__v": 0,
"responses": [
{
"_id": "59b6715725dcd2060da7f59c",
"response_text": "reponse 1 question 1 group 2",
"order": "1",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f596",
"__v": 0
},
{
"_id": "59b6715725dcd2060da7f59d",
"response_text": "reponse 2 question 1 group 2",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f596",
"__v": 0
}
]
},
{
"_id": "59b6715725dcd2060da7f597",
"question_text": "question 2 group 1",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"group": "59b6715725dcd2060da7f592",
"__v": 0,
"responses": [
{
"_id": "59b6715725dcd2060da7f59e",
"response_text": "reponse 1 question 2 group 2",
"order": "1",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f597",
"__v": 0
},
{
"_id": "59b6715725dcd2060da7f59f",
"response_text": "reponse 2 question 2 group 2",
"order": "2",
"created_date": "2017-09-11T11:19:51.710Z",
"question": "59b6715725dcd2060da7f597",
"__v": 0
}
]
}
]
}
]
}
]

有人可以帮我构建示例中所示的响应吗?

最佳答案

大多数情况下您需要使用 $group 使用 $unwind 处理后“重建”为了再次嵌套您的数组输出。还有一些提示:

   Survey.aggregate([
{ "$lookup": {
"from": Group.collection.name,
"localField": "_id",
"foreignField": "survey",
"as": "groups"
}},
{ "$unwind": "$groups" },
{ "$lookup": {
"from": Question.collection.name,
"localField": "groups.questions",
"foreignField": "_id",
"as": "groups.questions"
}},
{ "$unwind": "$groups.questions" },
{ "$lookup": {
"from": Response.collection.name,
"localField": "groups.questions._id",
"foreignField": "question",
"as": "groups.questions.responses"
}},
{ "$group": {
"_id": {
"_id": "$_id",
"company": "$company",
"created_date": "$created_date",
"enabled": "$enabled",
"name": "$name",
"groups": {
"_id": "$groups._id",
"name": "$groups.name",
"order": "$groups.order",
"created_date": "$groups.created_date",
"survey": "$groups.survey"
}
},
"questions": { "$push": "$groups.questions" }
}},
{ "$sort": { "_id": 1 } },
{ "$group": {
"_id": "$_id._id",
"company": { "$first": "$_id.company" },
"created_date": { "$first": "$_id.created_date" },
"enabled": { "$first": "$_id.enabled" },
"name": { "$first": "$_id.name" },
"groups": {
"$push": {
"_id": "$_id.groups._id",
"name": "$_id.groups.name",
"order": "$_id.groups.order",
"created_date": "$_id.groups.created_date",
"survey": "$_id.groups.survey",
"questions": "$questions"
}
}
}},
{ "$sort": { "_id": 1 } }
]);

所以这就是重建阵列的方法,你一步一步来,而不是试图一次性完成。这可能是最难普遍理解的概念,但“管道”意味着您实际上可以“多次”做事情,将一个 Action 链接到另一个 Action 的输出。

所以第一个 $group 是在“组”详细信息级别完成的,因为您想要 $push "questions" 的元素数组,这是 $unwind 最后一次“解构” .请注意 "responses"作为最后一个 $lookup 的结果仍然是一个数组阶段。但是除了数组内容之外,其他所有内容都在 _id 中。 “分组 key ”。

关于“秒” $group 你实际上使用像 $first 这样的操作符在 Survey 处构造特定的字段属性等级。 "groups"数组是用 $push 构造的再次,因此在前一阶段的“分组键”中的每个属性都以 _id 为前缀。 ,所以这就是它们在这里被引用的方式。

此外,从技术 Angular 来看,您应该始终 $sort 每次调用 $group 后如果您有预期的订单。不能保证按任何特定顺序对分组键进行收集(尽管通常是相反的堆栈顺序)。如果您需要订单,请指定它,尤其是在应用 $push 时在 $group 之后重建一个数组.

没有 $sort 的原因 之前 初始 $group 是因为前面的流水线阶段实际上对现有订单没有任何影响。所以发现的顺序总是被保留下来。

一些提示:
  • 诸如 Group.collection.name 之类的东西实际上使用 mongoose 模型上定义的属性来执行诸如“获取集合名称”之类的操作。这使您免于硬编码到 $lookup 本身并与代码运行时在模型上注册的任何内容保持一致。
  • 如果您打算将属性输出为数组,或者甚至在模式上具有某个名称的现有“引用数组”,则“保留该名称”。为路径创建临时名称确实没有多大意义,除非您在管道阶段专门为以后阶段“重新排序”字段的输出而这样做。否则,只需在所有情况下使用您打算输出的名称。以这种方式阅读和解释意图要容易得多。
  • 除非你真的是这个意思,否则不要使用像 preserveNullAndEmptyArrays 这样的选项。 . $lookup 的组合还有一个“特殊方式” + $unwind 实际处理,并且真正在“单个阶段”中执行,而不是在“展开”之前检索所有结果。您可以在聚合管道的“解释”输出中看到这一点。简而言之,如果您总是有关系匹配,则不要使用该选项。最好不要。


  • 示范

    作为完整的 list 和概念证明,我们可以加载源 JSON,将其存储在数据库中单独的集合中,然后使用聚合语句来检索和重建所需的结构:
    const fs = require('fs'),
    mongoose = require('mongoose'),
    Schema = mongoose.Schema;

    mongoose.Promise = global.Promise;
    mongoose.set('debug',true);

    const uri = 'mongodb://localhost/nested',
    options = { useMongoClient: true };

    const responseSchema = new Schema({
    response_text: String,
    order: String,
    created_date: Date,
    question: { type: Schema.Types.ObjectId, ref: 'Question' }
    });

    const questionSchema = new Schema({
    question_text: String,
    order: String,
    created_date: Date,
    group: { type: Schema.Types.ObjectId, ref: 'Group' }
    });

    const groupSchema = new Schema({
    name: String,
    order: String,
    created_date: Date,
    survey: { type: Schema.Types.ObjectId, ref: 'Survey' },
    questions: [{ type: Schema.Types.ObjectId, ref: 'Question' }]
    });

    const surveySchema = new Schema({
    company: { type: Schema.Types.ObjectId, ref: 'Company' },
    created_date: Date,
    enabled: Boolean,
    name: String
    });

    const companySchema = new Schema({

    });

    const Company = mongoose.model('Company', companySchema);
    const Survey = mongoose.model('Survey', surveySchema);
    const Group = mongoose.model('Group', groupSchema);
    const Question = mongoose.model('Question', questionSchema);
    const Response = mongoose.model('Response', responseSchema);


    function log(data) {
    console.log(JSON.stringify(data,undefined,2))
    }

    (async function() {

    try {

    const conn = await mongoose.connect(uri,options);

    await Promise.all(
    Object.keys(conn.models).map( m => conn.models[m].remove() )
    );

    // Initialize data
    let content = JSON.parse(fs.readFileSync('./jsonSurveys.json'));
    //log(content);

    for ( let item of content ) {

    let survey = await Survey.create(item);
    let company = await Company.create({ _id: survey.company });

    for ( let group of item.groups ) {
    await Group.create(group);
    for ( let question of group.questions ) {
    await Question.create(question);
    for ( let response of question.responses ) {
    await Response.create(response);
    }
    }
    }

    }

    // Run aggregation

    let results = await Survey.aggregate([
    { "$lookup": {
    "from": Group.collection.name,
    "localField": "_id",
    "foreignField": "survey",
    "as": "groups"
    }},
    { "$unwind": "$groups" },
    { "$lookup": {
    "from": Question.collection.name,
    "localField": "groups.questions",
    "foreignField": "_id",
    "as": "groups.questions"
    }},
    { "$unwind": "$groups.questions" },
    { "$lookup": {
    "from": Response.collection.name,
    "localField": "groups.questions._id",
    "foreignField": "question",
    "as": "groups.questions.responses"
    }},
    { "$group": {
    "_id": {
    "_id": "$_id",
    "company": "$company",
    "created_date": "$created_date",
    "enabled": "$enabled",
    "name": "$name",
    "groups": {
    "_id": "$groups._id",
    "name": "$groups.name",
    "order": "$groups.order",
    "created_date": "$groups.created_date",
    "survey": "$groups.survey"
    }
    },
    "questions": { "$push": "$groups.questions" }
    }},
    { "$sort": { "_id": 1 } },
    { "$group": {
    "_id": "$_id._id",
    "company": { "$first": "$_id.company" },
    "created_date": { "$first": "$_id.created_date" },
    "enabled": { "$first": "$_id.enabled" },
    "name": { "$first": "$_id.name" },
    "groups": {
    "$push": {
    "_id": "$_id.groups._id",
    "name": "$_id.groups.name",
    "order": "$_id.groups.order",
    "created_date": "$_id.groups.created_date",
    "survey": "$_id.groups.survey",
    "questions": "$questions"
    }
    }
    }},
    { "$sort": { "_id": 1 } }
    ]);

    log(results);

    } catch(e) {
    console.error(e);
    } finally {
    mongoose.disconnect();
    }


    })();

    替代案例

    另外值得注意的是,通过对 .populate() 的嵌套调用,只需进行一些小的架构更改,即可获得相同的结果。 :
      let alternate = await Survey.find().populate({
    path: 'groups',
    populate: {
    path: 'questions',
    populate: {
    path: 'responses'
    }
    }
    });

    虽然它看起来简单得多,但它实际上引入了更多负载,因为它向数据库发出多个查询以检索数据,而不是在单个调用中:
    Mongoose: groups.find({ survey: { '$in': [ ObjectId("59b6715725dcd2060da7f591") ] } }, { fields: {} })
    Mongoose: questions.find({ _id: { '$in': [ ObjectId("59b6715725dcd2060da7f594"), ObjectId("59b6715725dcd2060da7f595"), ObjectId("59b6715725dcd2060da7f596"), ObjectId("59b6715725dcd2060da7f597") ] } }, { fields: {} })
    Mongoose: responses.find({ question: { '$in': [ ObjectId("59b6715725dcd2060da7f594"), ObjectId("59b6715725dcd2060da7f595"), ObjectId("59b6715725dcd2060da7f596"), ObjectId("59b6715725dcd2060da7f597") ] } }, { fields: {} })

    您可以看到模式更改(只是为连接添加虚拟字段)以及修改后的列表中的操作代码:
    const fs = require('fs'),
    mongoose = require('mongoose'),
    Schema = mongoose.Schema;

    mongoose.Promise = global.Promise;
    mongoose.set('debug',true);

    const uri = 'mongodb://localhost/nested',
    options = { useMongoClient: true };

    const responseSchema = new Schema({
    response_text: String,
    order: String,
    created_date: Date,
    question: { type: Schema.Types.ObjectId, ref: 'Question' }
    });

    const questionSchema = new Schema({
    question_text: String,
    order: String,
    created_date: Date,
    group: { type: Schema.Types.ObjectId, ref: 'Group' }
    },{
    toJSON: {
    virtuals: true,
    transform: function(doc,obj) {
    delete obj.id;
    return obj;
    }
    }
    });

    questionSchema.virtual('responses',{
    ref: 'Response',
    localField: '_id',
    foreignField: 'question'
    });

    const groupSchema = new Schema({
    name: String,
    order: String,
    created_date: Date,
    survey: { type: Schema.Types.ObjectId, ref: 'Survey' },
    questions: [{ type: Schema.Types.ObjectId, ref: 'Question' }]
    });

    const surveySchema = new Schema({
    company: { type: Schema.Types.ObjectId, ref: 'Company' },
    created_date: Date,
    enabled: Boolean,
    name: String
    },{
    toJSON: {
    virtuals: true,
    transform: function(doc,obj) {
    delete obj.id;
    return obj;
    }
    }
    });

    surveySchema.virtual('groups',{
    ref: 'Group',
    localField: '_id',
    foreignField: 'survey'
    });

    const companySchema = new Schema({

    });

    const Company = mongoose.model('Company', companySchema);
    const Survey = mongoose.model('Survey', surveySchema);
    const Group = mongoose.model('Group', groupSchema);
    const Question = mongoose.model('Question', questionSchema);
    const Response = mongoose.model('Response', responseSchema);


    function log(data) {
    console.log(JSON.stringify(data,undefined,2))
    }

    (async function() {

    try {

    const conn = await mongoose.connect(uri,options);

    await Promise.all(
    Object.keys(conn.models).map( m => conn.models[m].remove() )
    );

    // Initialize data
    let content = JSON.parse(fs.readFileSync('./jsonSurveys.json'));
    //log(content);

    for ( let item of content ) {

    let survey = await Survey.create(item);
    let company = await Company.create({ _id: survey.company });

    for ( let group of item.groups ) {
    await Group.create(group);
    for ( let question of group.questions ) {
    await Question.create(question);
    for ( let response of question.responses ) {
    await Response.create(response);
    }
    }
    }

    }

    // Run aggregation

    let results = await Survey.aggregate([
    { "$lookup": {
    "from": Group.collection.name,
    "localField": "_id",
    "foreignField": "survey",
    "as": "groups"
    }},
    { "$unwind": "$groups" },
    { "$lookup": {
    "from": Question.collection.name,
    "localField": "groups.questions",
    "foreignField": "_id",
    "as": "groups.questions"
    }},
    { "$unwind": "$groups.questions" },
    { "$lookup": {
    "from": Response.collection.name,
    "localField": "groups.questions._id",
    "foreignField": "question",
    "as": "groups.questions.responses"
    }},
    { "$group": {
    "_id": {
    "_id": "$_id",
    "company": "$company",
    "created_date": "$created_date",
    "enabled": "$enabled",
    "name": "$name",
    "groups": {
    "_id": "$groups._id",
    "name": "$groups.name",
    "order": "$groups.order",
    "created_date": "$groups.created_date",
    "survey": "$groups.survey"
    }
    },
    "questions": { "$push": "$groups.questions" }
    }},
    { "$sort": { "_id": 1 } },
    { "$group": {
    "_id": "$_id._id",
    "company": { "$first": "$_id.company" },
    "created_date": { "$first": "$_id.created_date" },
    "enabled": { "$first": "$_id.enabled" },
    "name": { "$first": "$_id.name" },
    "groups": {
    "$push": {
    "_id": "$_id.groups._id",
    "name": "$_id.groups.name",
    "order": "$_id.groups.order",
    "created_date": "$_id.groups.created_date",
    "survey": "$_id.groups.survey",
    "questions": "$questions"
    }
    }
    }},
    { "$sort": { "_id": 1 } }
    ]);

    log(results);

    let alternate = await Survey.find().populate({
    path: 'groups',
    populate: {
    path: 'questions',
    populate: {
    path: 'responses'
    }
    }
    });

    log(alternate);

    } catch(e) {
    console.error(e);
    } finally {
    mongoose.disconnect();
    }


    })();

    关于javascript - 来自多个集合的 $lookup 和嵌套输出,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46166589/

    25 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com