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

683 K Empty Slots

  Approach #1: Insert Into Sorted Structure [Accepted] Intuition Let's add flowers in the order they bloom. When each flower blooms, we check it's neighbors to see if they can satisfy the condition with the current flower. Algorithm We'll maintain  active , a sorted data structure containing every flower that has currently bloomed. When we add a flower to  active , we should check it's lower and higher neighbors. If some neighbor satisfies the condition, we know the condition occurred first on this day. Complexity Analysis Time Complexity (Java):  O(N \log N) O ( N lo g N ) , where  N N  is the length of  flowers . Every insertion and search is  O(\log N) O ( lo g N ) . Time Complexity (Python):  O(N^2) O ( N 2 ) . As above, except  list.insert  is  O(N) O ( N ) . Space Complexity:  O(N) O ( N ) , the size of  active . Approach #2: Min Queue [Accepted] Intuition For each contiguous block ("window") of  k  po...
Post a Comment
Read more