// 11/20/24 CSCI 4333.2


use tinker

// collection: test1
db.test1.insertOne(
   {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
   }
)

doc = {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
   }
doc
db.test1.insertOne(doc)

db.test1.find()

if (db.test1.find(doc).count() == 0) { db.test1.insertOne(doc) }


doc2 = {
      "StudentId" :1,
      "StudentName" : "Sarah Connor"
   }
doc2
if (db.test1.find(doc2).count() == 0) { db.test1.insertOne(doc2) }


show dbs
db.dropDatabase()
show dbs

// remove tinker
use tinker
db.test1.find()
doc = {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
}
doc
if (db.test1.find(doc).count() == 0) { db.test1.insertOne(doc) }
db.test1.find()
if (db.test1.find(doc).count() == 0) { db.test1.insertOne(doc) }
db.test1.find()


// create index

// db.collection.createIndex(keys, options, commitQuorum)
//keys: { "StudentId": 1 }; 1: true
// options:  { unique: true }
db.test1.createIndex( { "StudentId": 1 }, { unique: true } )
doc = {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
}
doc
db.test1.insertOne(doc)
db.test1.insertOne(doc)

doc2 = {
      "StudentId" :1,
      "StudentName" : "Sarah Smith"
   }
doc2
db.test1.insertOne(doc2)

doc3 = {
      "StudentId" :7,
      "StudentName" : "Linda Johnson"
   }
doc3
db.test1.insertOne(doc3)

db.test1.insertMany([
   {   "StudentId" :2,
      "GPA": 3.72
   },
   {   "StudentId" :3,
      "GPA": 1.69,
      "HairColor": "white"
   },
   {
      "BCAssetId": "78c22fc6-5dec-11ec-bf63-0242ac130002",
      "BCAssetType": "BCAssetTypeMetadata",
      "BCAssetName": "BCAssetTypeMetadata: MBSEModel",
      "ForBCAssetType": "MBSEModel",
      "Version": {
         "Version": "1.0",
         "Subversion": null,
         "StartTime": "2019-01-13T07:23:13+06:00"
      }
   }
])
db.test1.find()
// no arguments. 

// db.collection.find( <query>, <projection>, <options> )

db.student.find()

db.student.find({},
   { "_id": 0 }  
)
// <query>: {}
// <projection>: { "_id": 0 }  0: false

db.student.find({},
   { "_id": false }  
)

// Show all information of students majoring in 'CINF'.
// <query>: {"major": "CINF"}


db.student.find({"major": "CINF"},
    { "_id": 0 }
)

db.student.find({"mjor": "CINF"},
    { "_id": 0 }
)

db.student.find({"major": "cinf"},
    { "_id": 0 }
)

// Show all student names. Return an array of student objects.
db.student.find({},
   { "fname": 1, "lname":1, "_id": 0 }  
)

db.student.find({},
   { "fname": 1, "lname":1 }  
)

/*
Show all student names in this format:
student #0: Tony Hawk
student #1: Mary Hawk
student #2: David Hawk
student #3: Catherine Lim
student #4: Larry Johnson
student #5: Linda Johnson
student #6: Lillian Johnson
student #7: Ben Zico
student #8: Bill Ching
student #9: Linda King
Solution:

*/

result = db.student.find({},
   { "fname": 1, "lname":1, "_id": 0 }  
).toArray()

// May not always work as toArray() returns a promise,
// which may not be ready for use.
result.forEach((x,i) => console.log('student #' + String(i) + ': ' + x["fname"] + ' ' + x["lname"]))

result.forEach((x,i) => console.log('student #' + String(i+1) + ': ' + x["fname"] + ' ' + x["lname"]))

// [5] Show the names and credits (ach) of students majoring in 'CSCI' and having 40 or more credits.
db.student.find(
   { "major": "CSCI", "ach" : {$gte: 40} },
   { "fname": 1, "lname":1, "major": 1, "ach":1, "_id": 0 }  
)
// query:  { "major": "CSCI", "ach" : {$gte: 40} }  ach >= 40
// projection:  { "fname": 1, "lname":1, "major": 1, "ach":1, "_id": 0 } 

//[6] Show the first name and last name of students with a first name starting with a L or B, case insensitive.
db.student.find(
   { "fname": { $regex: /^[lb]/, $options: "i" } },
   { "fname": 1, "lname":1, "_id": 0 }  
)
// query:   { "fname": { $regex: /^[lb]/, $options: "i" } }
// $regex: regular expression: find pattern: /^[lb]/ in fname.
// pattern: ^[lb]; ^: starting position; [lb]: character class matches 1 char.

// [7] Show the names and credits (ach) of students majoring in 'CSCI' and having 40 or more credits.
db.student.find(
   { "$and": [ { "major": "CSCI"}, { "ach": {"$gte": 40}} ] },
   { "fname": 1, "lname":1, "ach":1, "_id": 0 }  
)


// aggregate method: pipelinbe
db.faculty.aggregate([
    {"$group" : {_id:"$deptCode", "count":{$sum:1}}}
])

db.faculty.aggregate([
    {"$group" : {_id:"deptCode", "count":{$sum:1}}}
])

db.faculty.aggregate(
   [  
      { $group: { "_id": "$deptCode", "count": {$sum:1}} },
      { $project: { "deptCode": "$_id" , "num_faculty": "$count",  "_id": 0}}
   ]
)


db.student.aggregate([
{$lookup:
    {
      from: "enroll",
      let: {joinValue: '$stuId'},
      pipeline: [
           { $match:
                 { $expr:
                    { $and:
                       [
                         { $eq: [ "$stuId",  "$$joinValue" ] },
                         { $eq: [ "$classId", 10000 ] }
                       ]
                    }
                 }
            }    
        ],
        as: "enrollment"     }},
  { $match: {"enrollment":  { $ne: [] }}},  
  { $project: { "fname": 1, "lname": 1, "_id": 0}} 
])