Archives pour la catégorie MongoDB

MongoDB quick query memo

MONGO SHELL

Connects to the FOO DB :

> use FOO

Connects to current DB (FOO) in the users collection and find out all items :

> db.users.find()

Connects to current DB (FOO) in the users collection and find out all items and print out in a pretty way :

> db.users.find().pretty()

Find the first item :

> db.users.findOne()

Find all items matching the query :

> db.users.find(<QUERY>)

Next page of the result iteration :

> it

Shows all the available databases :

> show databases

Set the profiling level (level (0: Off, 1:Slow queries, 2: All) ms for slow queries) :

> db.setProfilingLevel(1,4)
> db.getProfilingLevel()
> db.getProfilingStatus()

Shows the system logs for queries with « users.name » sorted by timestamp :

> db.system.profile.find({ns:/users.name/}).sort({ts:1})

Realtime MongoDB statistics in command line (Linux/Mac) :

$ mongostat

Realtime MongoDB top command line (Linux/Mac). Gets wich collections are used in the moment (Param : number of seconds of refresh). The results shows the time spend in each collection in the interval of the param :

$ mongotop 3

MONGO QUERIES

Print out all items where name matches with « Stefan » :

> db.users.find({name:"Stefan"})

Print out all items where name matches with « Stefan » and age with 43 :

> db.users.find({name:"Stefan", age:43})

Print out all items where name matches with « Stefan » and age less than 25 :

> db.users.find({name:"Stefan", age:{$lt:25}})

Print out all items where name matches with « Stefan » and age greater or equal to 35 :

> db.users.find({name:"Stefan", age:{$gte:35}})

All items where name= »Stefan » and having an « age » field :

> db.users.find({name:"Stefan", age:{$exists:true}})

All items having « String » type « name » field (See Bson spec) :

> db.users.find({name:{$type:2}})

All items having « name » field ending with « a » :

> db.users.find({name:{$regex:"a$"}})

All items having « Red » in the « favorite_color » array :

> db.users.find({favorite_color:"Red"})

All items having name matching « Stefan » or « Nils » :

> db.users.find({$or:[{name:"Stefan"},{name:"Nils"}]})

All items having name matching « Stefan » or with age over 40 :

> db.users.find({$and:[{name:"Stefan"},{age:{$gt:40}}]})

All items having name matching « Stefan » and with age over 40 :

> db.users.find({$elemMatch:[{name:"Stefan"},{age:{$gt:40}}]})

All items having « Stefan » and « Nina » in friends array :

> db.users.find({friends:{$all:["Stefan","Nina"]}})

All items having « Stefan » or « Nina » in friends array :

> db.users.find({friends:{$in:["Stefan","Nina"]}})

All items which name are « John » having friends aged over 18 :

> db.users.find({name:"John", friends.age:{$gt:18}})

Counts the number of items having name= »Stefan » :

> db.users.count({name:"Stefan"})

Update items having name= »Stefan » with values name= »Thomas » and age=52 (All other attributes will be lost) :

> db.users.update({name:"Stefan"},{name:"Thomas", age:52})

Update items having name= »Stefan » and change age value to 52. All the other attributes will stay unchanged :

> db.users.update({name:"Stefan"},{$set:{age:52}})

Update items having name= »Stefan » and change age value to 52. If there is no « Kevin » record, a new one will be created :

> db.users.update({name:"Kevin"},{$set:{age:52}}, {upsert:true})

Update the first item found that matches and change its age value to 52. All the other attributes will stay unchanged :

> db.users.update({},{$set:{age:52}})

Update all items and change age value to 52. All the other attributes will stay unchanged :

> db.users.update({},{$set:{age:52}}, {multi:true})

Update items having name= »Stefan » and increase the age value from 1 (52 -> 53) :

> db.users.update({name:"Stefan"},{$inc:{age:1}})

Update items having name= »Stefan » and remove the « age » attribute (Any value will work, not just 1) :

> db.users.update({name:"Stefan"},{$unset:{age:1}})

Modifies the 3rd element of « favorite_colors » collection for « Green » value :

> db.users.update({name:"Stefan"},{$set:{favorite_colors.2:"Green"}})

Add the « Pink » value to the favorite_color array :

> db.users.update({name:"Stefan"},{$push:{favorite_colors:"Pink"}})

Remove value from the right side of the array :

> db.users.update({name:"Stefan"},{$pop:{favorite_colors:1}})

Remove value from the left side of the array :

> db.users.update({name:"Stefan"},{$pop:{favorite_colors:-1}})

Add the elements to the favorite_color array :

> db.users.update({name:"Stefan"},{$pushAll:{favorite_colors:["Pink","Orange","Brown"]}})

Remove the Blue and Red values from the array :

> db.users.update({name:"Stefan"},{$pullAll:{favorite_colors:["Blue","Red"]}})

Add a « Green » element in favorite_color array if it doesn »t exists other wise nothing happends :

> db.users.update({name:"Stefan"},{$addToSet:{favorite_colors:"Green"}})

Remove all items of the collection (Indexes will not be deleted) :

> db.users.remove({})

Remove all items of the collection (Truncate in SQL) :

> db.users.drop()

Remove all items of the collection matching the query (Delete in SQL) :

> db.users.remove({name:"Stefan"})

Remove just one item of the collection matching the query (Delete in SQL) :

> db.users.remove({name:"Stefan"}, {justeOne:true})

Cursors

Assign a result set to a variable named « cur » :

> cur = db.users.find()

Assign a result set to a variable named « cur » without printing result :

> cur = db.users.find(); null;

True if there are still elements :

> cur.hasNext()

Gives the next element :

> cur.next()

Print all the elements of the cur cursor :

> while(cur.hasNext()) printjson(cur.next());

Set a limit of 5 elements :

> cur.limit(5)

Sort in reverse order :

> cur.sort({name : -1})

Limits to 5 results and skip 2 visited items (Begins after 2nd item. Can be used for pagination) :

> cur.limit(5).skip(2)

Indexes

Create an index on « name » ascending and « hair color » descending (single keys but also works with multipleKeys (Arrays)) :

> db.users.createIndex({name:1, hair_color:-1})

Create an index on subdocument value :

> db.users.createIndex({"friends.name":1})

Create a unique index on value :

> db.users.createIndex({name:1}, {unique:true})

Create a unique index on value only for items having all the attributes name and phone (others will not be indexed) :

> db.users.createIndex({name:1, phone:1}, {unique:true, sparse:true})

Create an index in the background. Users (write/read) will not be blocked. Creating this indexes is longer than in foreground (default) :

> db.users.createIndex({"friends.name":1}, {background:true})

Drop an index on « name » ascending and « hair color » descending :

> db.users.dropIndex({name:1, hair_color:-1})

View all indexes of a specific collection :

> db.users.getIndexes()

Shows execution plan to see if there is an index used to execute this query :

> db.users.explain().find({name:'Stefan'})

Shows execution plan with execution stats (Nb of documents, time…) to see if there is an index used to execute this query :

> db.users.explain({"executionStats"}).find({name:'Stefan'})

Shows execution plan of all possibilities (Nb of documents, time…) to see if there is an index used to execute this query :

> db.users.explain({"allPlansExecution"}).find({name:'Stefan'})

Shows the collection statistics (number of indexes …) :

> db.users.stats()

Shows the total index size of a collection (part of statistics) :

> db.users.totalIndexSize()

Geospatial indexes (2D indexes : in this case we use a « location » attribute of x,y values) :

> db.users.createIndex("location":"2D",type:1)
> db.users.find({location:{"$near":[74,140]}}).limit(3);

Geospatial spherical (GeoJson -> 2D indexes over a sphere : in this case we use a « location » attribute of latitude and longitude values) :

> db.users.createIndex("location":"2Dsphere",type:1)
> db.users.find({"location":{"$near":{"$geometry":{type: Point, Coordinates: [-63.12, 98.45]}, "$maxDistance": 2000}}}).limit(3);

Full text indexes, once created you can make queries findings items having a certain word in it (By default MDB uses an ‘or’ keyword between the strings in the query. TextSScore is used to sort the responses by pertinance of the order in the query. It is used to find the best match) :

> db.users.createIndex("myTextAttribute":"Text")
> db.users.find({"$text":{$search : "home dog time"}}, {score : {$meta : "textScore"}}).sort(score: {$meta: "textScore"});

Find with a specefic index (Here we’re looking for users in certain groups. We sort by user_id ascending but force tu use the group_id index in memory and not on disk to optimize) :

> db.users.find({user_id:{$gt:500000}, group_id:54}).sort({user_id:1}).hint({group_id:1})

Aggregation Framework

Group by category and sum items

> db.products.aggregate([{$group:{_id:"$category", num_products:{$sum:1}}}])

Group by « c » attribute

> db.stuff.aggregate([{$group:{_id:'$c'}}])

Group by and create new _id

> db.stuff.aggregate([{$group: {_id: {'moe':'$a', 'larry':'$b', 'curly':'$c' } } }])

Group by state and sum population

> db.zips.aggregate([{$group:{_id:"$state",population:{$sum:"$pop"}}}])

Group by state and get average population

> db.zips.aggregate([{$group:{_id:"$state",population:{$avg:"$pop"}}}])

Group by state and make array of postal codes (Lines to array)

> db.zips.aggregate([{$group:{_id:"$city", postal_codes:{$addToSet:"$_id"}}}]);

Group by and make array : 2 different ways

> db.zips.aggregate([{"$group":{"_id":"$city", "postal_codes":{"$push":"$_id"}}}])
> db.zips.aggregate([{"$group":{"_id":"$city", "postal_codes":{"$addToSet":"$_id"}}}])

Group by state and get max population

> db.zips.aggregate([{$group:{_id:"$state", pop:{$max:"$pop"}}}]);

Double grouping (Group of grouped elements with $min and $max values)

> db.fun.aggregate([{$group:{_id:{a:"$a", b:"$b"}, c:{$max:"$c"}}}, {$group:{_id:"$_id.a", c:{$min:"$c"}}}])

Project attributes in other attributes (Change name to lowerCase for example)

> db.zips.aggregate([{$project:{_id:0, city:{$toLower:"$city"}, pop:"$pop", state:"$state", zip:"$_id"}}])

Find elements that matches population greeater than 10000

> db.zips.aggregate([{$match:{pop:{$gt:10000}}}])

Sorting by state and then city

> db.zips.aggregate([{$sort:{state:1, city:1}}])

Aggregation pipline with $limit and $skip

> db.zips.aggregate([
    {$match: { state:"NY" } },
    {$group: { _id: "$city", population: {$sum:"$pop"}, } },
    {$project: { _id: 0, city: "$_id", population: 1, } },
    {$sort: { population:-1 } },
    {$limit: 5},
    {$skip: 10} 
])

Find first element in reverse order

> db.fun.aggregate([
    {$match:{a:0}},
    {$sort:{c:-1}}, 
    {$group:{_id:"$a", c:{$first:"$c"}}}
])

Array to lines (Array of tags will give one line for each element. New element will be a tags attribute)

> db.zips.aggregate([{$group:{$unwind:"$tags"}} ]);

Double $unwind if multiple arrays

> db.inventory.aggregate([
    {$unwind: "$sizes"},
    {$unwind: "$colors"},
    {$group: { '_id': "$name", 'sizes': {$addToSet: "$sizes"}, 'colors': {$addToSet: "$colors"} } } ])

Select count(*)

> db.zips.aggregate([{$group:{_id:null, count: {$sum:1} } } ]);

Select sum(price)

> db.zips.aggregate([{$group:{_id:null, total: {$sum:"$price"} } } ]);

Get substring of an attribute

> db.zips.aggregate([
    {$project: 
     {
	first_char: {$substr : ["$city",0,1]},
     }	 
   }
])

Replica Set

Create Replica Set

#!/usr/bin/env bash

mkdir -p /data/rs1 /data/rs2 /data/rs3
mongod --replSet m101 --logpath "1.log" --dbpath /data/rs1 --port 27017 --oplogSize 64 --fork --smallfiles
mongod --replSet m101 --logpath "2.log" --dbpath /data/rs2 --port 27018 --oplogSize 64 --smallfiles --fork
mongod --replSet m101 --logpath "3.log" --dbpath /data/rs3 --port 27019 --oplogSize 64 --smallfiles --fork

In Mongo configure Replica Set

> config = { _id: "m101", members:[
          { _id : 0, host : "localhost:27017"},
          { _id : 1, host : "localhost:27018"},
          { _id : 2, host : "localhost:27019"} ]
};

> rs.initiate(config);
> rs.status();
Publicités