Implementing Subquery in mongodb

Working with Data
,
#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??

#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.

#3

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

#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

#5

db.PREREQUISITE.insertMany([
{
“Course_number”:“CS3380”,
“Prerequisite_number”:“CS3320”
},
{
“Course_number”:“CS3380”,
“Prerequisite_number”:“MATH2410”
},
{
“Course_number”:“CS3320”,
“Prerequisite_number”:“CS1310”
}
])

#6

@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