M_G1
(M G)
February 28, 2022, 12:12am
1
I have two documents:
db.COURSE.insertMany([
{
"Course_name":"Intro to Computer Science",
"Course_number":"CS1310",
"Credit_hours":4,
"Department":"CS"
},
{
"Course_name":"Data Structures",
"Course_number":"CS3320",
"Credit_hours":4,
"Department":"CS"
},
{
"Course_name":"Discrete Mathematics",
"Course_number":"MATH2410",
"Credit_hours":3,
"Department":"MATH"
},
{
"Course_name":"Database",
"Course_number":"CS3380",
"Credit_hours":3,
"Department":"CS"
}
])
db.PREREQUISITE.insertMany([
{
"Course_name":"Intro to Computer Science",
"Course_number":"CS1310",
"Credit_hours":4,
"Department":"CS"
},
{
"Course_name":"Data Structures",
"Course_number":"CS3320",
"Credit_hours":4,
"Department":"CS"
},
{
"Course_name":"Discrete Mathematics",
"Course_number":"MATH2410",
"Credit_hours":3,
"Department":"MATH"
},
{
"Course_name":"Database",
"Course_number":"CS3380",
"Credit_hours":3,
"Department":"CS"
}
])
I wanted to query to retrieve the course_name of the prerequisites of the “Database”
course.
How can we use subquery in Mongo DB??
steevej
(Steeve Juneau)
February 28, 2022, 1:33am
2
Post your documents again after reading Formatting code and log snippets in posts . We cannot use what you published easily because it does not end up being valid JSON.
Hi @M_G1 ,
I believe you are referring to a lookup that can be performed between collections using fields as references. The following aggregation should do the work using a $lookup stage:
db.COURSE.aggregate([{$match: {
Course_name: 'Database'
}}, {$lookup: {
from: 'PREREQUISITE',
localField: 'Course_name',
foreignField: 'Course_name',
as: 'PREREQUISITE'
}}])
{ _id: ObjectId("621cbe9863c5b12f4cda3db8"),
Course_name: 'Database',
Course_number: 'CS3380',
Credit_hours: 3,
Department: 'CS',
PREREQUISITE:
[ { _id: ObjectId("621cbea763c5b12f4cda3dbc"),
Course_name: 'Database',
Course_number: 'CS3380',
Credit_hours: 3,
Department: 'CS' } ] }
Thanks
Pavel
M_G1
(M G)
February 28, 2022, 12:54pm
4
Thank you for your reply, I need the course names of Database Prerequsite.
For Example Database course has two prerequisite courses, I want to display the names of these courses
M_G1
(M G)
February 28, 2022, 12:56pm
5
db.PREREQUISITE.insertMany([
{
“Course_number”:“CS3380”,
“Prerequisite_number”:“CS3320”
},
{
“Course_number”:“CS3380”,
“Prerequisite_number”:“MATH2410”
},
{
“Course_number”:“CS3320”,
“Prerequisite_number”:“CS1310”
}
])
@M_G1 ,
With the same idea just linking the “Course_number” field which seems to be the connecting field and than circle back to courses:
db.COURSE.aggregate([{$match: {
Course_name: 'Database'
}}, {$lookup: {
from: 'PREREQUISITE',
'let': {
course: '$Course_number'
},
pipeline: [
{
$match: {
$expr: {
$eq: [
'$$course',
'$Course_number'
]
}
}
},
{
$lookup: {
from: 'COURSE',
localField: 'Prerequisite_number',
foreignField: 'Course_number',
as: 'PREREQUISITE_COURSE'
}
},
{
$project: {
PREREQUISITE_COURSE: 1
}
}
],
as: 'PREREQUISITE'
}}])
However, looking at the schema I don’t see why not to store the prequiste inside a document for the course:
{
"Course_name":"Database",
"Course_number":"CS3380",
"Credit_hours":3,
"Department":"CS",
Prerequisites : [ { _id: ObjectId("621cbe9863c5b12f4cda3db6"),
Course_name: 'Data Structures',
Course_number: 'CS3320',
Credit_hours: 4,
Department: 'CS' } ] },
{ _id: ObjectId("621cbe9863c5b12f4cda3db7"),
Course_name: 'Discrete Mathematics',
Course_number: 'MATH2410',
Credit_hours: 3,
Department: 'MATH' } ]
}
Thanks
Pavel