Skip to main content

MongoDB — Queries at a Glance — 2


Types of MongoDB Queries at a Glance.
Run the following one by one in the terminal of the folder where databases is stored.
  1. mongo
    • Opens mongo shell
  2. show dbs
    • displays all databases
  3. use <database name>
    • used to get a database active
  4. db.<collection>.find().pretty()
    • show cursor of all data from a collection
  5. db.<collection>.find({<field>: <integer>}).pretty()
    • show cursor of data from a collection where <field> is <integer> value
  6. db.<collection>.find({<field>: {$gt: <integer>}}).pretty()
    • show cursor of data from a collection where <field> is greater than <integer> value
  7. db.<collection>.find({<field>: {$gte: <integer>}}).pretty()
    • show cursor of data from a collection where <field> is greater than or equal to <integer> value
  8. db.<collection>.findOne({<field>: <integer>})
    • show first document with <field> equal to <integer> value mentioned
  9. db.<collection>.find({<field>: {$ne: <integer>}}).pretty()
    • show cursor of data from a collection where <field> is not equal to <integer> value
  10. db.<collection>.find({<field>: {$lt: <integer>}}).pretty()
    • show cursor of data from a collection where <field> is less than <integer> value
  11. db.<collection>.find({<field>: {$lte: <integer>}}).pretty()
    • show cursor of data from a collection where <field> is less than or equal to <integer> value
  12. db.<collection>.find({"<field>.<embeded field>": {$gt: <integer>}}).pretty()
    • show cursor of data where <embeded field> of <field> is greater than <integer> value from a collection
  13. db.<collection>.find({<array field>: <value>}).pretty()
    • show cursor of data from a collection where <array field> has <value> in it
  14. db.<collection>.find({<array field>: [<value>]}).pretty()
    • show cursor of data from a collection where <array field> has exactly [<value>] in it
  15. db.<collection>.find({<field>: {$in: [<value1>, <value2>]}}).pretty()
    • show cursor of data from a collection where <field> either has value <value1> or <value2>
  16. db.<collection>.find({<field>: {$nin: [<value1>, <value2>]}}).pretty()
    • show cursor of data from a collection where <field> has value anything but <value1> or <value2>
  17. db.<collection>.find({<field>: {$lt: <value>}}).count()
    • show count of documents from a collection where <field> has value less than <value>
  18. db.<collection>.find({$or: [{<field1>: {$lt: <value1>}}, {<field1>: {$gt: <value2>}, {<field2>: {$gt: <value3>}]}).pretty()
    • show cursor of documents from a collection where <field1> has value less than <value1> or <field1> has value less than <value2> or <field2> has value greater than <value3>
  19. db.<collection>.find({$nor: [{<field1>: {$lt: <value1>}}, {<field1>: {$gt: <value2>}, {<field2>: {$gt: <value3>}]}).pretty()
    • show cursor of documents from a collection where <field1> does not have value less than <value1> or <field1> does not have value less than <value2> or <field2> does not have value greater than <value3>
    • It is the opposite of or
    • If we take count of the two queries above, if the first one returns x, the second one will return (count of all documents – x)
  20. db.<collection>.find({$and: [{<field1>: {$lt: <value1>}}, {<field2>: {$lt: <value2>}]}).pretty()
    • show cursor of documents from a collection where <field1> has value less than <value1> and <field2> has value less than <value2>
  21. db.<collection>.find({<field1>: {$lt: <value1>}, <field2>: {$lt: <value2>}}).pretty()
    • The previous query can be written in this form too
  22. db.<collection>.find({$and: [{<field1>: <value1>}, {<field1>: <value2>}]}).pretty()
    • This is an example where using 4and is a must
  23. db.<collection>.find({<field>: {$not: {$eq: 60}}}).count()
    • usage of $not
  24. db.<collection>.find({<field>: {$ne: 60}}).count()
    • The previous query can also be written with $ne
  25. db.<collection>.find({<field>: {$exists: true}}).pretty()
    • Returns all documents where <filed> is defined
  26. db.<collection>.find({<field>: {$exists: false}}).pretty()
    • Returns all documents where <field> is not defined
  27. db.<collection>.find({<field>: {$exists: true, $ne: null}}).pretty()
    • $exists can be combined with other comparison operators
    • The above example returns cursor to documents where field exist and has a valid value
  28. db.<collection>.find({<field>: {$type: "number"}}).pretty()
    1. returns cursor to documents where fields has value of type number
  29. db.<collection>.find({<field>: {$type: "double"}}).pretty()
    • All integers will also be shown since shell only knows doubles
  30. db.<collection>.find({<field>: {$type: ["double", "string"]}}).pretty()
    • returns cursor to documents where field type is double or string
  31. db.<collection>.find({<field>: {$regex: /<key>/}}).pretty()

  32. When to use $elemMatch?
    • {
  _id: 1,
  name: { first: 'John', last: 'Backus' },
  birth: new Date('Dec 03, 1924'),
  death: new Date('Mar 17, 2007'),
  contribs: [ 'Fortran', 'ALGOL', 'Backus-Naur Form', 'FP' ],
  awards: [
            { award: 'National Medal',
              year: 1975,
              by: 'NSF' },
            { award: 'Turing Award',
              year: 1977,
              by: 'ACM' }
          ]
}
    • Problem: Find the person who has award 'Nation Medal' and must be awarded in year 1975
    • Solution:
    • db.users.find({awards: {$elemMatch: {award:'National Medal', year:1975}}})
    • $elemMatch allows you to match more than one component within the same array element.
    • Without $elemMatch mongo will look for users with National Medal in some year and some award in 1975s, but not for users with National Medal in 1975.
  33. When to use $expr and $cond?
    • {
 "_id": 1,
 "volume": 89,
 "target": 80
}
{
      "_id": 2,
"volume": 200,
"target": 117
}
{
      "_id": 3,
"volume": 100,
"target": 117
}
    • Problem: Find documents where value of volume field is greater than value of target field
    • Solution:
    • db.<collection>.find({$expr: {$gt: ["$volume", "$target"]}}).pretty()
    • Problem: Volume greater than 190 and difference between Volume and Target to be a minimum of 10
    • Solution:
    • db.<collection>.find({$expr: {$gt: [{$cond: {if: {$gte: ["$volume", 190]}, then: {$subtract: ["$volume", 10]}, else: "$volume"}}, "$target"]}}).pretty()
  34. How to import data from json file to MongoDB?
    • mongoimport <file name>.json -d <databse name> -c <collection name> --drop --jsonArray
  35. db.<collection>.find({"<array>.<field of object>": <value>}).pretty()
    • returns cursor to group of documents where in the <array>, one of the <object>'s <field> has the <value>
  36. db.<collection>.find({hobbies: {$size: 3}}).pretty()
    • Only exact matches supported
  37. Find the count of documents in a collection where a field's first subfield's value is "Value1" and the field's second subfield's value is greater than or equal to Value2.
    • Wrong way: db.<collection>.find({$and: [{"<field>.<subfield>": "Value1"}, {"<field>.<subfield>": {$gte: <Value2>}}]}).count()
      • Output: 2
    • Right way: db.<collection>.find({<field>: {$elemMatch: {<subfield1>: "<Value1>", <subfield2>: {$gte: Value2}}}}).pretty()
  38. Find documents in a collection where a field has n number of elements in its array
    • db.<collection>.find({<field>: {$size: <n>}}).pretty()
  39. Find within an embedded document
    • db.<collection>.find({"<field>.<subfield>": "Value"}).pretty()
  40. Check if one value of an array has two or more conditions
    • Right way: db.<collection>.find({field: {$elemMatch: {$gt: 8, $lt: 10}}}).pretty()
    • Wrong way: db.<collection>.find({field: {$and: [{$gt: 8, $lt: 10}]}}).pretty()
  41. Sorting Cursor - Sort entire collection with one field in ascending order and within that sort by another field in descending order
    • db.<collection>.find().sort({"<field1.subfield>": 1, <field2>: -1}).pretty()
  42. Skip and Limit
    • db.<collection>.find().skip(10).limit(20).pretty()
      • Skip skips the number of documents and then fetches
      • Limit limits the number of documents it fetches
      • If sort is used, skip and limit always applies on sorted result
  43. Projection — Get result with only specified fields and not all fields
      • db.<collection>.find(<filer>, <projection>)
    • db.<collection>.find({}, {<field1>: 1, "<field2>.<subfield>": 1})
      • Id is always included in the result
    • db.<collection>.find({}, {<field1>: 1, "<field2>.<subfield>": 1, _id: 0})
      • To exclude id, add _id: 0 to the query explicitly
  44. Filter and Projection
    • db.<collection>.find({<field>: "Value1"}, {"<field>.$": 1}).pretty()
    • db.<collection>.find({<field>: {$all: ["Value1", "Value2"]}}, {"<field>.$": 1}).pretty()
    • db.<collection>.find({<field>: "Value1"}, {<field>: {$elemMatch: {$eq: "Value2"}}}).pretty()
  45. Project from and to in an array
    • db.<collection>.find({"<field1>.<subfield>": {$gt: <n>}}, {<arrayfield2>: {$slice: [1,2]}, <field3>: 1}).pretty()
      • This drops the element at 0 index and displays elements from index 1 to 2

Comments

Post a Comment

Popular posts from this blog

Python - List - Append, Count, Extend, Index, Insert, Pop, Remove, Reverse, Sort

🐍 Advance List List is widely used and it's functionalities are heavily useful. Append Adds one element at the end of the list. Syntax list1.append(value) Input l1 = [1, 2, 3] l1.append(4) l1 Output [1, 2, 3, 4] append can be used to add any datatype in a list. It can even add list inside list. Caution: Append does not return anything. It just appends the list. Count .count(value) counts the number of occurrences of an element in the list. Syntax list1.count(value) Input l1 = [1, 2, 3, 4, 3] l1.count(3) Output 2 It returns 0 if the value is not found in the list. Extend .count(value) counts the number of occurrences of an element in the list. Syntax list1.extend(list) Input l1 = [1, 2, 3] l1.extend([4, 5]) Output [1, 2, 3, 4, 5] If we use append, entire list will be added to the first list like one element. Extend, i nstead of considering a list as one element, it joins the two lists one after other. Append works in the following way. Input l1 = [1, 2, 3] l1.append([4, 5]) Output...
Post a Comment
Read more

Difference between .exec() and .execPopulate() in Mongoose?

Here I answer what is the difference between .exec() and .execPopulate() in Mongoose? .exec() is used with a query while .execPopulate() is used with a document Syntax for .exec() is as follows: Model.query() . populate ( 'field' ) . exec () // returns promise . then ( function ( document ) { console . log ( document ); }); Syntax for .execPopulate() is as follows: fetchedDocument . populate ( 'field' ) . execPopulate () // returns promise . then ( function ( document ) { console . log ( document ); }); When working with individual document use .execPopulate(), for model query use .exec(). Both returns a promise. One can do without .exec() or .execPopulate() but then has to pass a callback in populate.
1 comment
Read more

Python Class to Calculate Distance and Slope of a Line with Coordinates as Input

🐍  Can be run on Jupyter Notebook #CLASS DESIGNED TO CREATE OBJECTS THAT TAKES COORDINATES AND CALCULATES DISTANCE AND SLOPE class Line:     def __init__(self,coor1,coor2):         self.coor1=coor1         self.coor2=coor2 #FUNCTION CALCULATES DISTANCE     def distance(self):         return ((self.coor2[0]-self.coor1[0])**2+(self.coor2[1]-self.coor1[1])**2)**0.5 #FUNCTION CALCULATES SLOPE         def slope(self):         return (self.coor2[1]-self.coor1[1])/(self.coor2[0]-self.coor1[0]) #DEFINING COORDINATES coordinate1 = (3,2) coordinate2 = (8,10) #CREATING OBJECT OF LINE CLASS li = Line(coordinate1,coordinate2) #CALLING DISTANCE FUNCTION li.distance() #CALLING SLOPE FUNCTION li.slope()
Post a Comment
Read more