mongodb and mongoose
C -> insertMany([{},{}]), insertOne({})
R -> find(), findOne()
U -> updateMany(), updateOne()
D -> deleteMany([]), deleteOne()
mongosh # mongodb shell
show dbs # show all db present in local system
use <name_of_db> # 1. if db is present --> the use it , else create a new db specifed by you
show collections # 2. shows all collection(table) of selected db
db # current db
db.dropDataBase() # drop current db
cls # clear terminal
exit # exit mongodb shell
# commands
# db -> database
# users -> collection(table)
# {name:'karthik'} -> document(row)
# no schema is required for no of colums , no same fields is req in each document
# dynamic fields in each document
# automatically generate id -> _id
db.users.insertOne({name:'karthik'}) # insert 1 document
db.users.insertMany([{name:'amey'},{name:'karthik'}])
db.users.find() # display all document
db.users.find().limit(2) # display only top 2 document
db.users.find().sort({name:1}).limit(2) # sort document in Asc order and display only top 2 document
db.users.find().sort({name: -1}).limit(2) # sort document in Dsc order and display only top 2 document
db.users.find().skip(1).limit(2) # skip the 1st matched document
# where clause(SQL) = --> return document based on mached condition
db.users.find({name:'amey'}) # return document where name is amey
# select (sql)--> selcet only a certain fields from document
db.users.find({name:'amey'}, {name: 1, age: 1}) # return document where name is amey , and display only name and age field
db.users.find({name:'amey'}, {name: 1, age: 1, _id:0})
# where clause(SQL) (=,>,<,>=,<=,!=)
# = $eq -> db.users.find({age:{$eq: 15}}) # age = 15
# > $gt -> db.users.find({age:{$gt: 15}}) # age > 15
# < $lt -> db.users.find({age:{$lt: 15}}) # age < 15
# >= $gte -> db.users.find({age:{$gte: 15}}) # age >= 15
# <= $lte -> db.users.find({age:{$lte: 15}}) # age <= 15
# != $ne -> db.users.find({age:{$ne: 15}}) # age != 15
# $exist -> db.users.find({age:{$exist: true/false}})
# $in -> db.users.find({name:{$in: ['Goku','Karthik']}})
# $nin -> db.users.find({name:{$nin: ['Goku','Karthik']}})
db.users.find({name:{$eq: 'amey'}}) # name ='amey'
db.users.find({name:{$ne: 'amey'}}) # name !='amey'
# where clause(SQL) (and, or,)
db.users.find({age:{$gte: 20 , $lte:40}}) # (age >= 20 and age<=40)
db.users.find({age:{$gte: 20 , $lte:40}, name: 'amey'}) # (age >= 20 and age<=40) and name ='amey'
db.users.find({$and : [{age: 26},{name:'amey'}]}) # age = 26 and name ='amey'
db.users.find({$or : [{age: {$gte : 20}},{name:'amey'}]}) # age >= 20 or name ='amey'
# to compare 2 fields inside the documents
db.users.find({$expr: {$gt:['$debt','$balance']}}) # debt > balance
db.users.findOne({age:{$lte: 15}}) # age<=15 -> 1st matched record
db.users.countDocuments({age:{$lte: 15}}) # age<=15 -> total matched record Number
# db.users.updateOne({age: 15},{$set: {age:27}}) # age=15 -> age=27
db.users.updateOne({_id: ObjectId('42414131373')},{$set: {name: 'new name'}})
db.users.findOne({_id: ObjectId('42414131373')})
db.users.updateOne({_id: ObjectId('42414131373')},{$inc: {age : 3} }) # increment age by 3
db.users.updateOne({_id: ObjectId('42414131373')},{$rename: {name : 'fullName'} }) #update field name
#update array --> push and pull
db.users.updateOne({_id: ObjectId('42414131373')},{$push: {hobbies : 'Bowling'} }) # hobbies: ['Bowling']
db.users.updateOne({_id: ObjectId('42414131373')},{$pull: {hobbies : 'Bowling'} }) # hobbies: []
db.users.updateMany({address:{$exist: true},{$unset : {address: ''}}}) # update all matched document -> where address exist, then remove all of them
db.users.deleteOne({age:{$eq: 15}}) # delete 1st matched document -> age=15
db.users.deleteMany({age:{$exist: false}}) # delete all matched document -> age field dont exist
# db.employee.aggregate([{},{},{}])
db.teachers.insertMany([
{ "_id": 1, "name": "John Doe", "age": 35, "gender": "male" },
{ "_id": 2, "name": "Jane Smith", "age": 40, "gender": "female" },
{ "_id": 3, "name": "Michael Johnson", "age": 45, "gender": "male" },
{ "_id": 4, "name": "Emily Williams", "age": 30, "gender": "female" },
{ "_id": 5, "name": "Robert Brown", "age": 38, "gender": "male" },
{ "_id": 6, "name": "Emma Jones", "age": 33, "gender": "female" },
{ "_id": 7, "name": "William Davis", "age": 37, "gender": "male" },
{ "_id": 8, "name": "Olivia Miller", "age": 41, "gender": "female" },
{ "_id": 9, "name": "David Wilson", "age": 36, "gender": "male" },
{ "_id": 10, "name": "Sophia Moore", "age": 32, "gender": "female" },
{ "_id": 11, "name": "Richard Taylor", "age": 39, "gender": "male" },
{ "_id": 12, "name": "Isabella Anderson", "age": 43, "gender": "female" },
{ "_id": 13, "name": "Joseph Thomas", "age": 34, "gender": "male" },
{ "_id": 14, "name": "Mia Jackson", "age": 42, "gender": "female" },
{ "_id": 15, "name": "Charles White", "age": 36, "gender": "male" },
{ "_id": 16, "name": "Abigail Harris", "age": 31, "gender": "female" },
{ "_id": 17, "name": "Daniel Martin", "age": 44, "gender": "male" },
{ "_id": 18, "name": "Evelyn Thompson", "age": 38, "gender": "female" },
{ "_id": 19, "name": "Matthew Garcia", "age": 37, "gender": "male" },
{ "_id": 20, "name": "Sofia Martinez", "age": 35, "gender": "female" },
{ "_id": 21, "name": "Andrew Robinson", "age": 40, "gender": "male" },
{ "_id": 22, "name": "Grace Clark", "age": 33, "gender": "female" },
{ "_id": 23, "name": "Joshua Rodriguez", "age": 39, "gender": "male" },
{ "_id": 24, "name": "Avery Lewis", "age": 42, "gender": "female" },
{ "_id": 25, "name": "Christopher Lee", "age": 37, "gender": "male" },
{ "_id": 26, "name": "Chloe Walker", "age": 31, "gender": "female" },
{ "_id": 27, "name": "Kevin Hall", "age": 44, "gender": "male" },
{ "_id": 28, "name": "Zoey Allen", "age": 38, "gender": "female" },
{ "_id": 29, "name": "Brian Young", "age": 36, "gender": "male" },
{ "_id": 30, "name": "Harper King", "age": 34, "gender": "female" }
])
# fetch all teachers whos gender is male only
db.teachers.aggregate([{
$match:{gender: 'male'}
}])
# group teachers by age
db.teachers.aggregate([{
$group:{_id: '$age'}
}])
# group teachers by age and show all teachers name per age group
db.teachers.aggregate([{
$group:{_id: '$age', names :{ $push: '$name'}}
}])
# group teachers by age and show complete document per age group
db.teachers.aggregate([{
$group:{_id: '$age', comDoc :{ $push: '$$ROOT'}}
}])
# give a count per age of male teachers
db.teachers.aggregate([
{ $match:{ gender: 'male'}},
{ $group:{ _id: '$age', countTeacherInThisAgeGroup :{ $sum: 1 }}} # $sum: 1 -> increment by 1
])
# give a count per age of male teachers and sort them by count in desc order
db.teachers.aggregate([
{ $match:{ gender: 'male'}},
{ $group:{ _id: '$age', countTeacherInThisAgeGroup :{ $sum: 1 }}}, # $sum: 1 -> increment by 1
{ $sort: { countTeacherInThisAgeGroup : -1}}
])
db.teachers.aggregate([
{ $match:{ gender: 'male'}},
{ $group:{ _id: '$age', sumOfTeacherInThisAgeGroup :{ $sum: { $toDouble : '$age'} }}} # $sum: 1 -> increment by 1
])
# find hobbies per age group
db.teachers.aggregate([{
$group:{_id: '$age', hobbies :{ $push: '$Hobbies'}}
}])
# find hobbies per age group --> dont want it in nested array
db.students.aggregate([
{ $unwind : '$Hobbies'},
{ $group:{_id: '$age', hobbies :{ $push: '$Hobbies'}}}
])
# find no of student per each hobbies
db.students.aggregate([
{ $unwind : '$Hobbies'},
{ $group:{_id: '$Hobbies', hobbiesCount :{ $sum: 1}}}
])
#find avg age of all students
db.students.aggregate([{
$group:{_id: null, avgAge :{ $avg: '$age'}}
}])
# find total no of hobbies for all the student in collection
db.students.aggregate([
{ $unwind : '$Hobbies'},
{ $group:{_id: null, hobbiesCount :{ $sum: 1}}}
])
db.students.aggregate([
{ $group:{_id: null, hobbiesCount :{$sum: {$size: '$Hobbies'}}}}
])
db.students.aggregate([
{ $group:{_id: null, hobbiesCount :{$sum: {$size:{$ifNull: ['$Hobbies',[]]} }}}}
])
# list all hobbies
db.students.aggregate([
{ $unwind : '$Hobbies'},
{ $group:{_id: null, allHobbies :{ $addToSet: '$Hobbies'}}} # addToSet -> remove duplicute values
])
$match, $group, $unwind, $filter
$filter:{
input: '$scores', # field name
as: 'score', # displayed field name
cond: { $gt :['$age', 20]} , # codn -> age>20
}
$lookup --> ~joins in SQL(combine 2 tables with common_id between them)
inner -> common(matched) rows ,2 tables with common_id between them
left outer -> all rows from left table
right outer -> all rows from right table
full outer -> all rows from left table and right table
# $lookup -> left outer join
$lookup:{
from :"", # right document
localField :"", # left doc id field name
foreignField: "", # right doc id field name
as:"" # name of newly created field (displayed field name)
}
# customer left joins orders
db.customer.aggregate([{
$lookup:{
from:'orders',
localField:'_id',
foreignField:'customer_id',
as:'OrderDetails'
}
}])