javascript - $lookup from Multiple Collections, and nested output -
i have multiple collections , used separate collection & foreign key approach , , want join collections build nested collections. schemas of collections:
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'} }); and code build nested object:
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" }} }} ]) i structure below, ( external link ):
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 } ] } ] } ] } ] can me structure response shown in sample please?
mostly need use $group "reconstruct" after processing $unwind in order nest array output again. there couple of tips:
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 } } ]); so that's approach rebuilding arrays, take 1 step @ time rather trying in 1 go. it's difficult of concepts comprehend, "pipeline" means can in fact things "multiple times", chaining 1 action output of other.
so first $group done @ "groups" level of detail because want $push items of "questions" array, last "deconstructed" $unwind. note "responses" still array result of last $lookup stage. aside array content, else goes in _id "grouping key".
on "second" $group use operators $first construct specific field properties @ survey level. "groups" array constructed $push again, , every property in "grouping key" of previous stage therefore prefixed _id, that's how referenced here.
also, technical standpoint should $sort after each invocation of $group if have expected order. collection on grouping keys not guaranteed in specific order ( though typically it's reverse stack order ). if expect order, specify it, , particularly when applying $push reconstruct array following $group.
the reason why there no $sort before initial $group because preceeding pipeline stages don't have effect on existing order. order of discovery preserved.
a couple of tips:
things
group.collection.nameuse properties defined on mongoose models things "get collection name". saves hard-coding$lookup, stays consistent whatever registered on model @ time code run.if intend output of property array or have existing "array of reference" on schema name, "keep name". making interim names paths not make lot of sense unless doing in pipeline stage purposes of "re-ordering" output of fields in later stage. otherwise, use name intend output in cases. it's easier read , interpret intent way.
unless mean it, don't use options
preservenullandemptyarrays. there "special way" combination of$lookup+$unwindhandled, , gets executed in "single stage" rather retrieving results before "unwinding". can see in "explain" output aggregation pipeline. in short, if have relational matches, don't use option. it's more optimal not to.
demonstration
as complete listing , proof of concept, can load in source json, store in database in separate collections , use aggregation statement in order retrieve , reconstruct desired structure:
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); ( let item of content ) { let survey = await survey.create(item); let company = await company.create({ _id: survey.company }); ( let group of item.groups ) { await group.create(group); ( let question of group.questions ) { await question.create(question); ( 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); } { mongoose.disconnect(); } })(); alternate case
also worth noting few small schema changes, same result can achieved using nested calls .populate():
let alternate = await survey.find().populate({ path: 'groups', populate: { path: 'questions', populate: { path: 'responses' } } }); whilst looks lot more simple, it's introducing more load due fact issues multiple queries database in order retrieve data, , not in single call:
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: {} }) you can see schema changes ( addition of virtual fields joins ) along code in action in amended listing:
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); ( let item of content ) { let survey = await survey.create(item); let company = await company.create({ _id: survey.company }); ( let group of item.groups ) { await group.create(group); ( let question of group.questions ) { await question.create(question); ( 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); } { mongoose.disconnect(); } })();
Comments
Post a Comment