// 2024_4_22.js

// Mongosh
mongosh.exe

// Set up toyu DB in Mongo.
mongorestore --archive="toyu-db.gz" --gzip --nsFrom='toyu.*' --nsTo='toyu.*'

// [1] Show all students.
use toyu

db.student.find()
// db.collection.find(query, projection, options)

// Getting rid of _id:
// query: {}
//  projection: { "_id": 0 } 
db.student.find({},
   { "_id": 0 }  
)

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

// Show all information of students majoring in 'CINF'.

// query: {"major": "CINF"}
//  projection: { "_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 }  
)


SELECT <<projection>> in mongodb find: db.collection.find(query, projection, options) return cursor
FROM <<db.collection>>
WHERE <<query>>


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, "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 }  
)

// [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 }  
)

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}}
   ]
)

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

// [9] Show the names of students who have enrolled in 10000: joining two document.
This should have the similar effect of the SQL statement:

SELECT DISTINCT s.fname, s.lname
FROM student AS s, enroll AS e
WHERE s.stuId = e.stuId AND e.classId = 10000;

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


F23 HW #8
[5] Repeat [4] except that the result is shown in textual format with the
department names, code, number of faculty, and number of staff in the following
manner. Be mindful of details and order.

[4] show only those departments with 2 or

[1] Computer Information Systems (CINF): number of faculty 2; number of staff 5
 [2] Information Technology (ITEC): number of faculty 2; number of staff 4
 [3] Computer Science (CSCI): number of faculty 4; number of staff 12

SQL:

SELECT d.deptCode, d.deptName, COUNT(f.facId) as numFaculty, 
    d.numStaff
FROM department AS d INNER faculty AS f ON (d.deptCode = f.deptCode)
GROUP BY d.deptCode, d.deptName, d.numStaff
HAVING numFaculty >= 2;


//Mongo:

result = db["faculty"].aggregate(
	[	
		{ $group: { "_id": "$deptCode", "count": {$count: {}} }}, // GROUP BY + numFaculty
		{ $match: { "count": { $gte: 2}}},  // HAVING numFaculty >= 2
		{ $project: { "deptCode": "$_id" , "numFaculty": "$count",  "_id": 0 }},
		{ $sort: { "numFaculty": -1, "_id": 1 }}
	]
).toArray()

deptResult = db.department.find(
	{},
	{  "deptCode":1, "deptName":1, "numStaff": 1, "_id": 0 }	
).toArray();

var deptName = {};
var numStaff = {};
deptResult.forEach((x,i) => {
	deptName[x["deptCode"]] = x["deptName"];
	numStaff [x["deptCode"]] = x["numStaff"]
})

result.forEach((x,i) => console.log('  [' + String(i+1) + ']  ' + deptName[x["deptCode"]] +
	" (" + x["deptCode"] + '): number of faculty ' + x["numFaculty"] + '; number of staff ' + numStaff[x["deptCode"]]))
	


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

show dbs
db.dropDatabase()
show dbs

db.test1.find()

doc =   {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
   }

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


// remove tinker
show dbs
db.dropDatabase()
show dbs
// create index
db.test1.createIndex( { "StudentId": 1 }, { unique: true } )
doc = {
      "StudentId" :1,
      "StudentName" : "Joseph Connor"
}
doc
db.test1.insertOne(doc)
db.test1.insertOne(doc)

db.test1.find()


db.test1.insertMany([
   {   "StudentId" :2,
      "GPA": 3.72
   },
   {   "StudentId" :3,
      "GPA": 1.69
   },
   {
      "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()