#MongoDB
####PYMONGO a=grades.find({'type': 'homework'}).sort('student', pymongo.ASCENDING).sort('score', pymongo.ASCENDING) doc=foo.find(query).hint([('c', pymongo.ASCENDING)]).explain()#arrays of tuples db.cities.save(newdoc) #create or replace the newdoc into db
####PYMONGO AND UDACITY EXAMPLES
pipeline = [
{"$unwind": "$isPartOf"},
{"$group": {"_id": {"country": "$country","isPartOf" : "$isPartOf"},
"avgPopulation": {"$avg" : "$population"}}},
{ "$group" : {"_id" : "$_id.country",
"avgRegionalPopulation": {"$avg" : "$avgPopulation"} }}
]
pipeline = [
{ "$group" : {"_id" : "$source",
"count": {"$sum" : 1}}},
{"$sort": {"count" : -1}}
]
pipeline = [
{ "$match" : { "user.time_zone" : "Brasilia",
"user.statuses_count" : {"$gte" : 100}}},
{ "$project" : { "followers": "$user.followers_count",
"screen_name": "$user.screen_name",
"tweets": "$user.statuses_count" }},
{"$sort" : {"followers" : -1}},
{"$limit" : 1}
]
pipeline = [
{"$group" : {"_id" : "$user.screen_name",
"count" : {"$sum" : 1},
"tweet_texts": {"$push" : "$text"}}
},
{"$sort" : {"count" : -1}},
{"$limit": 5}
]
pipeline = [
{"$match" : { "country" : "India"}},
{"$unwind": "$isPartOf"},
{"$group": {"_id": "$isPartOf",
"count": {"$sum" : 1}}},
{"$sort": {"count" : -1}},
{"$limit": 1}
]
pipeline = [
{"$match" : { "country" : "India"}},
{"$unwind": "$isPartOf"},
{"$group": {"_id": "$isPartOf",
"avgPopulation": {"$avg" : "$population"}}},
{ "$group" : {"_id" : "India Regional City Population Average",
"avg": {"$avg" : "$avgPopulation"} }}
]
####QUERRYING use (muda o db para o passado) db.find.pretty() (apresenta resultado de maneira estruturada) db.find({campo : {$gt/$gte/$lt/$lte: procurado}}) $or: db.scores.find({$or: [{score: {$lt: 50}},{score: {$gt: 90}}]}) $and: db.scores.find({$and: [{score: {$lt: 50}},{score: {$gt: 90}}]}) #tem outro jeito de fazer $exists e $regex: db.users.find({email: {$exists: true}, name: {$regex: "q"}})# existis serve para retornar docs que contenham o campo $all, $in: and e or para listas db.catalog.find({price : {$gt: 10000}, "reviews.rating": {$gte: 5}}) db.scores.count({type: "essay", score: {$gt: 90}}) db.places.find({location: {$near: [longitude,latitude]}}).limit(20) db.places.find({location: {$near:{$geometry:{type: 'point', coordinates: [0,0]}, $maxDistance: 2000}}}) .find({$text: {$search: "bla"}})#Quando index do tipe "text" .find({$text: {$search: "bla"}}).score({$meta: 'textScore'}).sort({$meta: 'textScore'})#Quando index do tipe "text"
####AGGREGATION
####AGGREGATION key-word: pipe-line sql: select manufaturer, count (*) from products, group by manufaturer #o $sum serve para somar 1 quando encontra manufacturer e insere numa variavel num_products mongo: db.products.aggregate([{$group:{_id: "$manufacturer",num_products: {$sum:1}}}] )
- $project - reshape
- $match - filter
- $group - aggregate
- $sort - sort
- $skip - skips
- $limit - limits
- $unwind - normalize
- $out - output (other collection)
- $geonear
sql: select manufacturer, category, count(*) from products group by manufacturer, category
mongo: db.products.aggregate([{$group:{_id: { "a": "$manufacturer", "b": "$category"},num_products: {$sum:1}}}] )
mongo:db.populations.aggregate([{$group:{_id: "$city",postal_codes:{$addToSet:"$_id"}}}] )
mongo: db.grades.aggregate([{'$group':{_id: {class_id: '$class_id', student_id: '$student_id'}, 'average': {'$avg': "$score"}}},{'$group':{_id: "$_id.class_id", 'average':{'$avg': "$average"} }}]) #double group stages
####More operators:
- $push
- $addtoset -$push is similar to $addToSet. The difference is that rather than accumulating only unique values it aggregates all values into an array.
#####1-1 opperation db.populations.aggregate([{$project:{_id: 0,city: {$toLower:"$city"}, 'pop': 1, 'state': 1,'zip': '$_id'}}] )
######n-1 opperation
SQL: [WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT, SUM(), COUNT(), join]
MONGO: [$match, $group, $match, $project, $sort, $limit, $sum, $sum, no corresponding, but can be done throw $unwind in some cases]
->Expressions $sum, $avg, $min, $man, $push, $addToSet, $first, $last: must be used in conjunction with a sort
####IMPORT DATA mongoimport -db dbname -c collectionname --file input-file.json mongoimport -db example_udacity -c cities --type csv --headerline --file cities2.csv
####UPDATING db.users.update({"_id": "myrnarackham"},{$set: {country: "RU"}}) db.users.update({"_id": "myrnarackham"},{$inc: {population: 1000}}) db.scores.update({score: {$lt: 70}},{$inc:{score: 20} },{multi: true}) db.users.update({"_id": "jimmy"},{$unset: {interests: 1}}) db.scores.remove({score: {$lt: 60}}) db.foo.update({username:'bar'}, {'$set':{'interests':['cat', 'dog']}}, {upsert: true} )
####CURSOR cur=db.find(); null; cur.limit(20) cur.hasNext() cur.next() cur.sort({name: -1})# ordem alfabetica contraria cur.skip(2);null; db.scores.find({type: "exam"}).sort({score: -1}).skip(50).limit(20)
####GENÉRICO --->> para quando não tiver dado ctrl+c no mongod antes de fechar o terminal
kill $(pidof mongod)
ou
> use admin > db.shutdownServer()
---->> explorando pelo terminal use Show dbs Show collections
db.mycollection.stats()#mostra estatisticas sobre minha collection, incluindo o espaco em disco
db.mycollection.totalIndexSize()#mostra o espaco em disco dos meus index
db.foo.getProfilingStatus()
db.foo.setProfilingstatus(1,4)
db.foo.getProfilingStatus()#desliga
####INDEX
db.students.ensureIndex({'class':1,'student_name':1})#cria index na collection. Melhora performance mas penaliza update
db.students.indexes.find()#mostra todos os index que existem na collection
db.students.dropIndex({'student_name':1})#deleta index
db.students.ensureIndex({'student_id': 1, 'class_id': 1 },{unique: true})#unique index
db.students.ensureIndex({'student_id': 1, 'class_id': 1 },{unique: true, sparse: true})#unique sparse index. Quando nao tem o key em todos os docs. Quanto usar o sort, precisa usar o .hint({}) tb.
db.students.getIndex()#que index existem
db.foo.find({a:100}).hint({$natural: 1})#use no index
db.foo.find({c:100}).hint({$natural: 1})#use no index
db.stores.ensureIndex({location: '2d', type: 1})# 2 dimmension index... Para geospatial indexes
db.stores.ensureIndex({bla:1 , type: text})#permite procurar por palavras dentro de frases