1 / 37

Basic Mongo queries

Basic Mongo queries. Maria Filippousi Requirements Analyst, Persado maria.filippousi@persado.com. 27 Feb 2013. About this session.

kerem
Download Presentation

Basic Mongo queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basic Mongo queries Maria Filippousi Requirements Analyst, Persado maria.filippousi@persado.com 27 Feb 2013

  2. About this session It’s an introduction to Mongo queries (entry level), coming from a non technical person that had to learn how to query in mongo in order to perform white box testing. • What is covered • List of most frequently used operators • Comparison • Element • Logical • CRUD operations in MongoDB • What is not covered • Complex query and update operators I have never used • Aggregation framework • Where to find more information • docs.mongodb.org • Google it…

  3. Create Operations

  4. And first comes CREATE • insert() is the primary method to insert document(s) into MongoDB collection. • If the collection does not exist, insert() creates the collection during the 1st insert. db.User.insert({_id=1,FName:”Jon”,Lname:”Gill”,address:{street:”Kastorias”,Number:4,town:”Gerakas”,city:”Athens”,zipCode:15344},Phone:[2106619486,6974235445]}) • You can insert a document without specifying _id field. In this case insert() method will add the _id field to the document and generate a unique ObjectId for the value. db.User.insert({FName:”Nick”,Lname:”Cave”}) The created document contains an _id field with the generated ObjectId value: {"_id“:ObjectId("50a1880488d113a4ae94a9a"),”Fname”:”Nick”,”LName”:”Cave”}

  5. CREATE multiple documents You can perform a bulk insert into a collection by passing an array of documents to the insert() method. db.User.insert[ { _id: 3, name: { FName: “Damon”, LName:”Albarn” }, address:{street:”Kastorias”,Number:4,town:”Gerakas”,city:”Athens”,zipCode:1534} Age:35} { _id: 4, name: {FName: “Lour”, LName:”Reed” }, address:{street:”Adanon”,Number:6,town:”Pallini”,city:”Athens”,zipCode:1535}} ] * Document with _id=3 contains field Age that does not appear in the other document.

  6. Read Operations

  7. How to select • The find() method selects documents in a collection and returns a cursor to the selected documents. Syntax: db.collection.find(query,projection) • Parameters of the find() method • Query: only used of you wish to specify selection criteria. To be omitted if you wish to return all documents in the collection. • Projection: only used if you wish to control which fields are to be returned, or the projection. For example: {field1:1, field2:0} • 1 to include. The _id field is always included even if not explicitly stated. • 0 to exclude • Returns: Cursor to the document that matches query criteria. If the projection argument is specified, the matching documents returned, will only contain _id and projection fields. Note: Executing the query directly on the mongo shell prompt, will automatically iterate cursor to display the first 20 documents. To continue iteration through the next batch of 20, type it

  8. Find() examples • Return all documents in a collection without specifying parameters db.User.find() or what you all know as SELECT * FROM User • Returns randomly a document in a collection without specifying parameters db.User.findOne() • To select documents that match selection criteria, specify query criteria, e.g. return all documents (first 20) in user collection where Age=30: db.User.find({Age:30}) orSELECT * FROM User WHERE Age=30 • Select documents that match selection criteria and project certain fields into the result set, specify query criteria and project parameter. E.g. return all documents in user collection where Age=30 and project only: _id, Name and Age. db.User.find({Age:30, {Name:1,Age:1}}) or SELECT Name, Age FROM User where Age=30

  9. Comparison operators • To express equalto (e.g. =) in the MongoDB query language, use JSON {key:value} and return documents where value of the field is equal to the specified value db.User.find({Name: “Maria”}) • $ne: value of the field is not equal to the specified value db.User.find({Name:{$ne: “Maria”}})->return all documents from user collection where Name IS NOT Maria, including those that do not contain the Name field. • $gt,$gte,$lt,$lte: selects those documents where the value of the field is greater than the specified value. db.User.find({Age:{$gte:20})->return all documents from user collection where Age>=20 db.User.find({Age:{$gte:20,$lt:40})-> return all documents where 20<=Age<40

  10. Comparison operators • $in: return documents that the field value equals any value in the specified array (e.g. <value1>, <value2>, etc.) db.User.find({Age:{$in:[20,30,40]}}) -> return all documents where Age field is either 20 or 30 or 40 **If the field holds an array then $in returns documents whose field holds an array that contains at least one element that matches a value in the specified array db.User.find({Phone:{$in:[645213242,2754125]}})-> Phone field holds an array in this case, i.e. all phone numbers of a user and query will return all documents where phone is at least one of the two values in the specified array. • $nin: return documents where field is not in specified array or does not exist db.User.find({Age:{$nin:[20,30]}})-> return all documents that Age is not 20 nor 30 as well as documents that do not include the Age field • If the field holds an array, $nin will return documents whose fields holds an array with no element equal to any of the values in the specified array.

  11. Element operators • $mod: will select the documents where the field value divided by the divisor has the specified remainder. {field:{$mod:[divisor,remainder]}}, for example: db.Product.find({Price:{$mod:[6,2]}})-> return documents in Product collection where price field modulo 6 equals 2, i.e. those with price=14, 50 etc • $exists: return documents that contain the field if boolean is true or not if boolean is false. db.User.find({Age:{$exists:false}}) db.User.find({Name:{$exists:true}}) Db.User.find({Age:{$exists:true,$nin:[25,35]}}->will return all documents for which Age field exists and its value does not equal to 25 nor 35.

  12. And since we are talking about $exists, what happened to us… • We select users for targeting based on certain criteria, i.e. operands on user attributes. • We wanted to distinguish between users that a particular field did not exist and those that this field was empty, i.e. Category 1: AGE is EMPTY Category2: AGE NOT EXISTS • In order to retrieve users in Category1 query was: db.User.find({AGE:null}) In error this query returned also users that AGE doesn’t exist db.User.find({AGE: {$exists:false}}) In order to select users just from Category1, correct query is: db.User.find({Age:{$type:10}}) or db.User.find({$and:[{Age:null},{Age:{$exists:true}}]}) more to follow on $and…

  13. So what is $type? • Selects the documents where the value of the field is the specified BSON type Syntax: {field:{$type:<BSONtype>}} db.User.find({Age: {$type:10}})-> return documents where Age is null • $type can be used to verify that all documents are of the same data type db.User.find({DateofBirth:{$not:{$type:9}}})-> should ideally return 0, i.e. there is no document where DateofBirth is not of Date Data type • available BSON types and their corresponding numbers

  14. Logical operators- $and • $and: performs a logical AND operation on an array of two or more expressions (e.g. <expression1>, <expression2>, etc.) and selects the documents that satisfy ALL the expressions in the array. If the first expression evaluates to false, the rest of the expressions will not be evaluated. db.Product.find.({$and:[{price:50},{sale:true},{type:”jacket”}]})->return documents in the Product collection where price=50 and sale=true and type=jacket • You can have the same result by specifying a comma separated list of expressions ( implicit AND). db.Product.find({price:50,sale:true, type:”jacket”:}) • If you want to use AND operation on the same field e.g. return all documents where price exists and is not 50, you can • Either use $and for the two separate expressions db.Product.find({price:{$ne:50, $exists:true}}) or • combine the operator expressions for the fielddb.Product.find({$and:[{price:{$ne:50}},{price:{$exists:true}}]})

  15. Logical operators - $or • Performs a logical OR operation on an array of expressions and returns the documents that satisfy at least one of the expressions. db.Product.find({price:{$gte:50},$or:[{faulty:true}, {qty:{$lt:10}}]}) -> return products of category shoes that their price is >=50 and field faulty is true or qty field value is less than 10. • Choose $in operator over the $or, when you wish to perform OR operation with expressions that are equality checks for the value of the same field db.Product.find({$or:[{qty:{$lt:10}},{faulty:true}],price{$in:[20,30]}})->return documents that qty is less than 10 OR field faulty is true AND price field equals 20 OR 30 • Remember when using indexes with $or, each clause of an $or query will execute in parallel db.Product.find({$or:[{faulty:false},{sale:true}]})-> instead of using a compound index,you would create one index on faulty i.e. db.Product.ensureIndex({faulty:1}) and another index on sale i.e. db.Product.ensureIndex({sale:1})

  16. And what about order? • In order to control order your query will return matching documents, just append the sort() method to a cursor. • For each field in the sort document, if the field’s corresponding value is positive, sort() will return query results in ascending order for that attribute, while if value is negative, in descending order. db.User.find().sort({Age:-1}) ->return all documents from user collection sorted by Age field in descending order. • $natural parameter returns items according ton their order on disk dB.User.find().sort({$natural:-1})->return documents in the reverse of the order on disk • When using $or operator with the sort() method in a query, the query will not use the indexes on the $or field. db.Product.find({$or:[price:{$lt:80},{sale:true}]}).sort({price:1})-> will not use the index on price nor on sale

  17. You might have a problem with sort() • When the sort option consumes more than 32MB, mongoDB will return an error! • To avoid requiring mongoDB perform large in-memory sort: • Create index on the field you are sorting • Use sort() along with limit() Db.User.find({region:”SW”}).sort(Age:1).limit(50)->return the first 50 documents from collection user where region=SW, sorting by Age in ascending order. • limit() specifies the maximum number of documents the cursor will return

  18. And what about embedded documents? • For example a post with comments { _id:3, title: “how to query in embedded documents”, Comments: [ {by: ‘jgill’, body: “some text”, date:ISODate(“2013-02-20T22:00:00Z”)}, {by: ‘dlewis’, body: “some text1”, date:ISODate(“2013-02-21T22:00:00Z”)}, {by: ‘asmith’, body: “some text2”, date:ISODate(“2013-02-20T19:00:00Z”)}, ] } Note: individual documents have a size limit. If you have an ever-growing array of data just like in this example, embedding it in another document might not be such a good design decision…

  19. And yet another example… • Or the address of a user… { _id:2, Name: “JohnG” Phone:[6978235447,2106618500], Address{ street: “Kastorias”, Number:4, town: “Gerakas”, city: “Athens”, zipCode: 15344 } } **If address frequently retrieved with the Name, it would be better to embed the address data.

  20. So how to query in such cases? • If you need to query on all comments made before 28/2 db.Post.find({comments.date: {$lte:ISODate(“2013-02-28T00:00:00Z”)}}) • If you need to query on all users living in Kastorias street but not in Gerakas db.User.find({$and:[{address.street: “Kastorias”},{address.city:{$ne: “Gerakas”}}]})

  21. Basic Data aggregation operations

  22. Cant get any more basic… • Count- returns the count of documents that would match a find() query. Just append the count() method to a find() query to return the number of matching documents. db.collection.find(<query>). count() Such an operation doesn’t actually perform the find(), but counts the results to be returned by find(). Can also be written as: db.collection.count(<query>) ; where query is optional and used if you wish to specify selection criteria to determine which documents in the collection to count. • Examples: db.User.count() ordb.User.find().count()-> count number of documents in User collection db.Product.find({price:{$gte:25,$lt:40},category:{$in:[women,jacket]}}).count() Equivalent to db.Product.count({price:{$gte:25,$lt:40},category:{$in:[women,jacket]}})

  23. Cant get any more basic… • distinct- finds the distinct values for a specific field within a single collection and returns results in an array db.Product.distinct(“price”)-> return array of distinct values of price field from all documents in the Product collection db.Product.distinct(“price”,{faulty:true,category: “shoes”})->return an array of distinct prices from all documents in Product collection where category=shoes and are faulty. db.User.distinct(“characteristics.haircolour”)->return an array of the distinct values of the field haircolour in the subdocument characteristics from all documents in the User collection

  24. Update operations

  25. Update a field in a document • Update operations modify existing records or documents in a collection. db.collection.update( <query>, <update>, <options> ) Where <query> corresponds to WHERE Statement in SQL <update> corresponds to SET statement • By default update() method updates a single document, just like you UPDATE in SQL with LIMIT 1. You can update all documents that match the query criteria by using the multi option. • Parameters of the update() method • query: selection criteria • Update: modifications to apply. • Options: specifies whether to perform an upsert and/or a multiple update.

  26. Update a field in a document • Update operations modify existing records or documents in a collection. db.collection.update( <query>, <update>, <options> ) Where <query> corresponds to WHERE Statement in SQL <update> corresponds to SET statement • By default update() method updates a single document, just like you UPDATE in SQL with LIMIT 1. You can update all documents that match the query criteria by using the multi option. • Parameters of the update() method • query: selection criteria for the update. Employs the same query selectors as already mentioned in find() method. • Update: modifications to apply. • Options: specifies whether to perform an upsert and/or a multiple update.

  27. Update a field in the document • If update parameter of update() method contains any update operators expressions such as the $set operator expression • Parameter should only contain update operator expressions • update() method will only update the corresponding fields in the document • To update value of a field, use $ set db.Product.update({_id=1},{$set:{price:30}})->updates value of specified field (price) for document with id=1 by setting its value to 30. db.User.update({_id:1},{$set:{‘address.street’: ‘CaledonianRoad’}->update document with id=1 by setting the value of the field street in the subdocument address to Caledonian Road.

  28. How to increment value of a field • $inc: increments value of a field by a specified amount. db.collection.update( <query>, { $inc: { field1: amount } } ); db.Product.update({sale:false},{$inc:{price:20}})-> update 1st document where sale =false by increasing value of price field by 20 **If the field being incremented is not numeric, an error will be raised! • If you wish to update all matching documents based on the query criteria, specify multi() in the update() method. db.Product.update({sale:false},{$inc:{price:20}},{multi:true})->increments price value by 20 for all documents that sale=false. More to follow on multi()… * Since mongoDB is non transactional, to ensure atomicity , use $inc, so that increments occur sequentially, i.e. only one update at a time and incremented value is updated and stored before incrementing it any further.

  29. Update arrays • If the element in an array field is to be updated, update() method can perform the update by specifying the position of the element in the array. **Keep in mind that arrays in mongoDb start from 0. db.User.update({_id=3},{$set:{“Phone.0”: “6937245222”}}) ->update value of the 1st element in Phones array for the document with _id=3 • If you do not know the position of the element in the array you can user the $ positional operator. In order to know which array element to update, the array field must be included in the query parameter. db.User.update({_id:3,”Phone”:”6937245200”},{$set:{“Phone.$”:“6937245222”})) for the document where _id=3 and Phone array contains element equal to 6937…, update the 1st matching document in the array to 6937.. • and if the array contains subdocuments db.Post.update({_id=3,”comments.body”:”sometext”},{$set:{“comments.$.body”: “some new text”}})

  30. Add new field to a document • Update parameter may contain fields that do not exist in the document. In such a case, update() method will add the new fields to the document. • For example if document with id=3 in user collection was { _id:3, Name: “JohnG”, Address{ street: “Kastorias”, Number:4, town: “Gerakas”, zipCode: 15344 } } db.User.update({_id:3},{$set:{eyecolour: “green”, “address.flatNumber”:6}}) ->update the document in User collection and add to it a new field eyecolour and set its value to green and a new field ‘flatNumber’ in the subdocument address

  31. Add an element to an array • If you wish to add a new element to a field holding an array, you should use the $push operator db.User.update({_id:3},{$push:{Phone: “210275100”}})->append ‘210..’ to the array for field Phone, for the document with Id=3 e.g. add business phone number to the user’s existing list of phone numbers (mobile and home number) • If the field is not an array $push will fail. • If the field specified in the $push statement doesn’t exist in the document being updated, the operation will add a new array with the specified field and value to the matched document. • If you wish to append several values to an array at once, you can use $pushAll db.User.update({_id:3},{$pushAll:{Phone: [“210275100”, “6974215555”]}}) If instead of specifying an array, you specify a single value, no harm done, $pushAll will behave just like $push.

  32. Remove a field from a document • While $set operator, adds a field or updates its value, $unsetoperator removes the field from the document. db.User.update({Name: “JonG”},{$unset:{eyecolour: “1”}->delete field ‘eyecolour’ from document where Name=JonG • Value of the field in the $unset statement has no impact i.e. we will have the same result in the above example if we do {$unset:{eyecolour: “”}} • In case document being updated matches the initial query, i.e. Name=JonG, but the field specified in the $unset operation does not exist, i.e. for document with Name=JonG, there is no field eyecolour, the statement will have no effect.

  33. Remove element from an array • As $push, appends elements to an array, $pull is used, when you wish to remove all instance of a value from an existing array. db.User.update({_id:3},{$pull:{Phone: “210275100”}})-> will remove ’210..’ from the array in Phone field for document in User collection with id=3. If the value specified existed more than once in the array, then all its instances would be removed from the array. • Just like you use $pushAll when you wish to append multiple values into an array, if you wish to perform the inverse operation i.e. remove multiple values, you can use $pullAll. db.User.update({_id:3},{$pullAll:{Phone: [“210275100”, “6974215555”]}}) ->will remove both specified values from the array in field Phone. • If you wish to remove first or last element of an array, you can use $pop db.User.update({_id:3},{$pop:{Phone:1}})->removes the last element in Phone array for document with Id=3 db.User.update({_id:3},{$pop:{Phone:-1}})-> removes the 1st element in array

  34. Update multiple documents • The default behaviour of update() method is to update a single document. If you wish to update all documents that match the query, you should use $multi in the options parameter. db.User.Update({Age:{$lte:18}},{$set:{category:”teens”}},{multi:true})->updates all documents in user collection where Age<=18 and sets category to teens. • You can replace an existing document with a new document, by specifying field and value pairs in the update argument. All fields will be updated, except for the _id field. db.User.Update({FName: “Jon”, LName:”Gill”}, {Fname:”Jonathan”,Lname:”Gillian”, Age:35, address:{street:”CaledonianRoad”, number:6,city:”London”,zipCode:NW6},Sex: “Male”, ARPU: High”,RegistrationDate: newDate(“Jan 1, 2012”)} queries the Usercollection for the first document in User Collection where Fname:Jon and Lname:Gill and replaces all but the _id field in the document with the fields in the <update> argument

  35. Upsert • Instead of having to query for existence of a record before performing update or insert you can use upsert(). • Update() operation can accept <upsert> as an option in the argument. db.collection.update( <query>, <update>, { upsert:true } ) • If the query matches an existing document(s), the upsert performs an update. • If the query matches no document in the collection, the upsert inserts a single document. db.User.update({LName:”Bowie”},{FName:“David”, Lname:”Bowie”, address:{street:”Warren”,Number:4,city:”London”,zipCode:NW119H},Phone:[07986830381,0171523842]},{upsert:true}) -> If there is no such document, then create new that will contain the fields and values specified in the<update> argument. Since the _id field is omitted, the operation will add the _id field and generates a unique ObjectId for its value.

  36. Removing documents from a collection • remove() method deletes documents from a collection. db.collection.remove(<query>,<justone>) Where <query> as per update method, specifies matching criteria <justone>, takes a boolean if you wish to remove a single document db.User.remove({“address.city”: /^Ath/ } db.Product.remove({price:{$gte:100}},1)-> removes a single document from product collection for which value of field price is >=100 • If you don’t specify which documents to remove i.e. <query> is empty, then all documents will be removed from the collection, but not the indexes! **drop() is suggested for large deletion operations and it drops from db the entire collection and indexes are removed. You can recreate the collection and rebuild the indexes.

  37. Thank you Questions ?

More Related