MongoDB queries

1. Mongo find explained better.
   Mongo queries to show why validation is a good thing.
2. Mongo queries compare to sql.
3. Aggregation you can't do with normal queries.
4. Explain and Atlas
5. Finding slow queries. currentOp and oplog
6. Concurrency and isolation

Mongo find explained better

1. The most important thing to remember is the function ":" is overloaded. Sometimes it can mean equal or "into" like an array. For example if you use db.col1.find({field1 : "123"}) if will return documents in the collection "col1" where the field "field1" is equal to "123" OR AND A BIG OR when the field "field1" is an an array and one of the elements is euqal to "123". Truly annoying if you don'tr know that, but powerful. I will try to list a brief list of overloaded operators in Mongo.
2. Another thing will documents is they may not have a field you are searching for. For example, if you want to know documents that have a bad value, well, not having a value might be bad. So you have to test if the field exists, what type it is, and if the value is bad. TODO: example:
3. You can force structure to your documents in 3.2 is later. TODO: put example here.
4. For aggregation pipline, it is nothing magical. Aggreation pipline is just making a series of queries based on what the first query does. The 2nd and later queries can delete field, add fields, do distinct and group, etc. The biggest thing for Aggregation Pipeline is to minimize the documents examined to improve speed and also so the later commands in the pipline can be faster by having less documents to analyze. The first query creates a temporary list of collections to be used by each step in the pipline (which can add and delete or modify fields).

Execute this setup in mongo shell or save it to a file called "mark1.js" and in the mongo shell load("mark1.js")

You will note the entire thing has to be in javascript for load to work. This is an example of validation, ttl, javascript, and when the purge happens. When you run the count, it also print the time, and that is the second every minute it purges indexes.

db = db.getSiblingDB('markNielsen');
db.overloaded.drop();

db.createCollection( "overloaded",  {
  validator: { i: {$type: "long"}, f: {$type: "double"}, s: {$type: "string"}, t: {$type: "date"} } 
} )

function fillData() {

  for( var i = 0; i < 100000; i++ ) {
    var f = i + 0.0001;
    var s = i.toString();
    var t = new Date();

    var u = i;	 	       
    var l = 0;
    if ( i % 15 == 0 ) { u = i.toString(); l = 1;}
    if ( i % 15 == 1 ) { u = i + 0.002;  l = 1;}
    if ( i % 15 == 2 ) { u = new Date();  l = 1;}
    if ( i % 15 == 3 ) { u = [i, i+1, i+2, i+3];  l = 1;}
    if ( i % 15 == 4 ) { u = {Blah1: i, Blah2: i+1, Blah3: i+2};  l = 1;}
    if ( i % 15 == 5 ) { l = 2;}
    if ( i % 15 == 6 ) { u = null;}		      
    if ( i % 15 == 7 ) { u = true;}
    if ( i % 15 == 8 ) { u = false;}		      
    if ( i % 15 == 9 ) { u = i+1;}
    if ( i % 15 == 10 ) { u = i+2;}
    if ( i % 15 == 11 ) { u = "removeme";}
		      
    var rm = new Date(); 
		      
    if ( i % 20 == 0 ) {
      if ( i % 100 == 0 ) {
            db.overloaded.insert( {i : NumberLong(i), f: f, s: s, t: t, u: u, lopsided: l, sometimes: i, removeme: rm } );
	}
      else  {db.overloaded.insert( {i : NumberLong(i), f: f, s: s, t: t, u: u, lopsided: l, sometimes: i} );}		       
      } 
    else    {db.overloaded.insert( {i : NumberLong(i), f: f, s: s, t: t, u: u, lopsided: l } );}

    // print something out every 1000		       
    if (i % 10000 == 0) {print("Count:" + i );}

    }
}

fillData()

db.overloaded.createIndexes([ {i: 1}, {f: 1},{s :1},{t:1},{u:1}], {background:true});
db.overloaded.createIndex({partial:1}, {partialFilterExpression: { sometimes: { $exists: true }}});
db.overloaded.createIndex({lopsided:1}, {partialFilterExpression: { lopsided: { $gte: 1}}});

// Certain entries will be removed starting after 10 seconds.
// This makes it to you have a small index to delete stuff from. 		      
db.overloaded.createIndex( {removeme: 1}, { partialFilterExpression: { removeme: {$exists: true}}, expireAfterSeconds: 10, background: true});
		      

And after you have done that, check the indexes and the types of the fields. Do this quickly to watch the ttl in work.

// Let's check indexes
db.overloaded.getIndexes();
// Let's check the fields		      
db.overloaded.aggregate([   { "$project": {"i":{"$type":"$i"},"f":{"$type":"$f"},"s":{"$type":"$s"},"t":{"$type":"$t"}} }, {$limit : 3}    ]);

print ("Watch ttl in action ...... this could take up to two minutes if it missed its window of every minute. ")
print("See the count says they are there.");

for( var i = 0; i < 120; i++ ) {   sleep(1000); v = db.overloaded.find({removeme: {$exists: 1}}).count(); d = new Date(); print (i + " " + v + " " + d)}

Mongo queries to show why validation is a good thing.

  { "i" : NumberLong(1), "f" : 1.0001, "s" : "1") }
  { "i" : NumberLong(2), "f" : 2.0001, "s" : "2") }
  { "i" : NumberLong(3), "f" : 3.0001, "s" : "3") }

  { "s" : "3", "u" : [ 3, 4, 5, 6 ] }
  { "s" : "5", "u" : 5 }

  { "u" : [ 3, 4, 5, 6 ], "u_type" : "array" }
  { "u" : 5, "u_type" : "double" }

{ "u" : 5 }

{"u" : 13 }

{ "u" : 14 }

{ "u" : 5 }

{ "u" : 5 }

{ "u" : [ 3, 4, 5, 6 ] }

{ "u" : [ 3, 4, 5, 6 ] }

Mongo queries compare to sql.

1. sql to mongo map: https://docs.mongodb.com/manual/reference/sql-aggregation-comparison/
2. Quick reference cards: https://www.mongodb.com/collateral/quick-reference-cards

Aggregation you can't do with normal queries.

Aggregation optimization

Explain and Atlas

Finding slow queries. currentOp and oplog

Concurrency and isolation

Even though this is the way mogno is, it switches locks on long runnning queries which can lead to inconsistent results. DO this:

wget http://www.menprojects.com/public/mongo/inconsistent.txt
mongo inconsistent.txt
mongo markNielsen --quiet --eval 'db.inconsistent.update({}, { $set: { v: 1} }, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 1} ,{_id: 100000} ]}, {$set : {v:1}}, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 2} ,{_id: 200000} ]}, {$set : {v:2}}, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 3} ,{_id: 300000} ]}, {$set : {v:3}}, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 4} ,{_id: 400000} ]}, {$set : {v:4}}, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 5} ,{_id: 500000} ]}, {$set : {v:4}}, {multi: true})' &
mongo markNielsen --quiet --eval 'db.inconsistent.update({$or :  [ {_id : 6} ,{_id: 600000} ]}, {$set : {v:5}}, {multi: true})' &

mongo markNielsen --quiet --eval 'db.inconsistent.find({v:1}).count()'
mongo markNielsen --quiet --eval 'db.inconsistent.find({v:2}).count()'
mongo markNielsen --quiet --eval 'db.inconsistent.find({v:3}).count()'
mongo markNielsen --quiet --eval 'db.inconsistent.find({v:4}).count()'
mongo markNielsen --quiet --eval 'db.inconsistent.find({v:5}).count()'
mongo markNielsen --quiet --eval 'db.inconsistent.find({v:6}).count()'
    

All the answers should be "2" if all write queries happen sequentially. However, if you see some 0 or 1 counts the big query didn't finish while the other wrote, and the big query overwrote them eventuallty. Especially with "1".

My results may differ than yours.

    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:1}).count()'
    1357932
    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:2}).count()'
    0
    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:3}).count()'
    1
    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:4}).count()'
    2
    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:5}).count()'
    1
    $ mongo markNielsen --quiet --eval 'db.inconsistent.find({v:6}).count()'
    0