Notes On MongoDB

$ mkdir -p /data/db # Default data dir
$ mongod

$ mongo
  MongoDB shell version v5.0.6
  connecting to: mongodb://127.0.0.1:27017/
  MongoDB server version: 5.0.6
  ================
  Warning: the "mongo" shell has been superseded by "mongosh",

> db
  test

>  show databases
   admin   0.000GB
   config  0.000GB
   dev     0.462GB
   local   0.000GB

> use local

> show tables      # Tables and Collections are synonymous
> show collections
  startup_log

> use newdb       # will create database. No separate command to create db
> show databases  # will not show empty databases.

> db.posts.insert({
     title: 'Post One',
     body: 'Body of post one',
     category: 'News',
     tags: ['news', 'events'],
     user: {
       name: 'John Doe',
       status: 'author'
     },
     date: Date()
   })

 > db.posts.insertMany([
     {
       title: 'Post Two',
       body: 'Body of post two',
       category: 'Technology',
       date: Date()
     },
     {
       title: 'Post Three',
       body: 'Body of post three',
       category: 'News',
       date: Date()
     },
     {
       title: 'Post Four',
       body: 'Body of post three',
       category: 'Entertainment',
       date: Date()
     }
   ])

# Collections are created on-the-fly.
> db.myNewCollection.insert( { "name" : "Thava", "msg" : "Hello!" })
WriteResult({ "nInserted" : 1 })

# You can create capped collection using createCollection method:
> db.createCollection("mySecondCollection", {
    capped : true,   // supports max document count
    size : 2,        // Max collection size is 2MB
    max : 2})        // Max count is 2 documents (max count to show)

  # Note: If you insert more documents than max count, then only the latest docs will show up,
          but old ones will be retained internally.


> db.myNewCollection.insertMany( [ { json1 }, {json2} ... ])
# List documents ...
> db.myNewCollection.insert( { "name" : "Thava2", "msg" : "Hello2!" })
> db.myNewCollection.find()

   { "_id" : ObjectId("6229c5d2f7b6acf25e81c6aa"), "name" : "Thava", "msg" : "Hello!" }
   { "_id" : ObjectId("6229c922f7b6acf25e81c6ab"), "name" : "Thava2", "msg" : "Hello2!" }

> db.myNewCollection.find().pretty()   # Pretty print.

# Filter condition with operators such as $eq, $ne, $gt, $lt, $lte, $gte has this syntax...
> db.myNewCollection.find( {  name: { $eq : 'Thava' } })

> db.myNewCollection.distinct( 'name' )
  [ "Thava", "Thava2" ]
> db.myNewCollection.find({name: /ava/} )
    ... both 2 documents displayed ...
> db.myNewCollection.distinct( 'name' ).count()
  2

# To update by specifying entire new set of attributes, do this:
> db.myNewCollection.update({name : 'Thava'}, {newName: 'New Me', planet: 'earth'});
# Note: The existing attribute name is gone after the update!!!

# To update specific field(s), use following form:
> db.myNewCollection.update({name : 'Thava'}, {$set: {age: 20}})

# Update does nothing if there is no match. However if you speciy {upsert:1},
# then it inserts on update even if there is no match!
# Following creates a new record with { name: 'Thava', age: 30 } !!!
> db.myNewCollection.update({name : 'Thava'}, {$set: {age: 20}}, {upsert:1})

   WriteResult({
       "nMatched" : 0,
       "nUpserted" : 1,
       "nModified" : 0,
       "_id" : ObjectId("622e0cce6abf50ff9f577040")
   })

> db.myNewCollection.find()
   { "_id" : ObjectId("622a436dc78724a63a99fe95"), "name" : "Thava2", "msg" : "Second Hello!" }
   { "_id" : ObjectId("622a4ae8c78724a63a99fe96"), "name" : "Thava5", "msg" : "Hello5!" }
   { "_id" : ObjectId("622e0cce6abf50ff9f577040"), "name" : "Thava", "age" : 20 }

# Following creates new record { age: 20 } only!
> db.myNewCollection.update({name : /Nothing/}, {$set: {age: 20}}, {upsert:1})

# Delete a field from document ...
# Note that 1 in {age:1} is a dummy value which is ignored.
> db.myNewCollection.update({name : 'Thava'}, {$unset: {age:1}})

# If you want multi-delete to occur ...
> db.myNewCollection.update({name : 'Thava'},       // Filter condition
                            {$unset: {age:1}},      // Which field(s) to unset
                            {multi: true});         // Can we delete more than 1 record?

> db.myNewCollection.insert( { "name" : "Thava2", "msg" : "Duplicate Hello22!", age: 22 })
> db.myNewCollection.insert( { "name" : "Thava2", "msg" : "Duplicate Hello25!", age: 25 })
> db.myNewCollection.aggregate([{$group: {_id: "$name", total_by_name: {$sum:1} }}])

{ "_id" : "Thava2", "total_by_name" : 3 }
{ "_id" : "Thava5", "total_by_name" : 1 }

> db.myNewCollection.aggregate([{$group: {_id: "$name", ages: {$push: "$age"}} }])
{ "_id" : "Thava5", "ages" : [ ] }
{ "_id" : "Thava2", "ages" : [ 22, 25 ] }

> db.myNewCollection.aggregate([{$group: {_id: "$name", min_age: {$min: "$age"}} }])
{ "_id" : "Thava5", "min_age" : null }
{ "_id" : "Thava2", "min_age" : 22 }

# Other Aggregate operations: $first (pickup first value), $last, $avg, $max, $addToSet, etc.
# Pipeline operations: $project, $match, $group, $sort, $skip, $limit, $unwind
# $unwind operation is reverse-group-by operation. Takes array field, creates multiple rows (documents)
#

# Text Search!
# A collection can only have one text search index, but that index can cover multiple fields.
# Atlas Search supports multiple text indexes per collection.
db.posts.find({
   $text: {
     $search: "\"Post O\""
     }
 })

# Remove document ...
> db.myNewCollection.remove({name: "Thava"});
> db.myNewCollection.find({});

# Drop all of collection and documents and indexes.
> db.myNewCollection.drop();

# To drop database itself ...
> db.dropDatabase()

db.posts.insert({
  title: 'Post One',
  body: 'Body of post one',
  category: 'News',
  tags: ['news', 'events'],
  user: {
    name: 'John Doe',
    status: 'author'
  },
  date: Date()
})

db.posts.insertMany([
  {
    title: 'Post Two',
    body: 'Body of post two',
    category: 'Technology',
    date: Date()
  },
  {
    title: 'Post Three',
    body: 'Body of post three',
    category: 'News',
    date: Date()
  },
  {
    title: 'Post Four',
    body: 'Body of post three',
    category: 'Entertainment',
    date: Date()
  }
])

# asc
db.posts.find().sort({ title: 1 }).pretty()
# desc
db.posts.find().sort({ title: -1 }).pretty()

db.posts.find().forEach(function(doc) {
  print("Blog Post: " + doc.title)
})

db.posts.find({ title: 'Post One' }, {
  title: 1,
  author: 1
})

db.posts.update({ title: 'Post Two' },
{
  title: 'Post Two',
  body: 'New body for post 2',
  date: Date()
},
{
  upsert: true,      // Insert if not found
})

db.posts.update({ title: 'Post Two' },
{
  $set: {
    body: 'Body for post 2',
    category: 'Technology'
  }
})

db.posts.update({ title: 'Post Two' },
{
  $inc: {
    likes: 5
  }
})

db.posts.update({ title: 'Post One' },
{
  $set: {
    comments: [
      {
        body: 'Comment One',
        user: 'Mary Williams',
        date: Date()
      },
      {
        body: 'Comment Two',
        user: 'Harry White',
        date: Date()
      }
    ]
  }
})

# Create an ascending String index on String value
db.posts.createIndex({ title: 1})

# Create a text index ...
db.posts.createIndex({ title: 'text' }, {name: "title_idx", default_language: "english"})

# Create a Compound text index on title and body...
db.posts.createIndex({ title: 'text', body : 'text' }, {sparse: true})

# The second arg options are:
    unique: true,
    sparse: true,
    expireAfterSeconds: 3600
    hidden: true,   // If true, it is hidden from query planner

db.posts.find({
  $text: {
    $search: "body post", // Will search compound index for either word match.
  }
}

# Full text indexes are by default case and diatrics insensitive.
db.posts.find({
  $text: {
    $search: "\"Post O\""     // String quote inside string means exact Phrase search.
    // $caseSenstive: true   // Will make it case sensitive text search. By default it is false.
    // diacriticSensitive: true // Default is false
  }
})

// Find either word body or post by should not contain "News" !
db.posts.find( { $text: { $search: "body post -News" } } )

// Sort results by search score

db.posts.find(
   { $text: { $search: "body post " } },     // Search either one word
   { score: { $meta: "textScore" } }         // Project score field. id is auto included.
).sort( { score: { $meta: "textScore" } } )  // {score:-1} means descending.
                                             // Here score to be treated as special meta type.

db.createCollection("students", {
  validator: {
      $jsonSchema: {
         bsonType: "object",
         required: [ "name", "year", "major", "address" ],
         properties: {
            name: {
               bsonType: "string",
               description: "must be a string and is required"
            },
            major: {
               enum: [ "Math", "English", "Computer Science", "History", null ],
               description: "can only be one of the enum values and is required"
            }, ...
         }
      }
   }
})

{
 "_id":ObjectId("52ffc33cd85242f436000001"),
 "name": "John Smith",
 "address": [ {
       "line1": "22 A, Indiana Apt",
       "pin": 123456
    }, ...
 ]
} 

{
   "_id":ObjectId("52ffc33cd85242f436000001"),
   "name": "John Smith",
   "address_ids": [
         ObjectId("..."),
         ObjectId("...")
   ],
 ]
} 

> db.myNewCollection.update({name : 'Thava10'},
                            {$set: {address_ids: [100, 200, 300]}},
                            {multi: true});
> let result = db.myNewCollection.findOne({"name": /Thava10/},{"address_ids":1})

> db.myAddress.insertMany([{_id: 100, line1 : 'Addr100 Line1', pin: 100001},
                           {_id: 200, line1 : 'Addr200 Line1', pin: 200001},
                           {_id: 300, line1 : 'Addr300 Line1', pin: 300001}
                           ]);
  { "acknowledged" : true, "insertedIds" : [ 100, 200, 300 ] }

> let addresses = db.myAddress.find({"_id":{"$in":result["address_ids"]}})
    { "_id" : 100, "line1" : "Addr100 Line1", "pin" : 100001 }
    { "_id" : 200, "line1" : "Addr200 Line1", "pin" : 200001 }
    { "_id" : 300, "line1" : "Addr300 Line1", "pin" : 300001 }

* $ref − This field specifies the collection of the referenced document
* $id − This field specifies the _id field of the referenced document
* $db − This optional field points to the database of the referenced document

{
   "_id":ObjectId("53402597d852426020000002"),
   "address": {
   "$ref": "address_home",
   "$id": ObjectId("534009e4d852427820000002"),
   "$db": "tutorialspoint"},
   "contact": "987654321",
   "dob": "01-01-1991",
   "name": "Tom Benzamin"
}

> let user = db.users.findOne({"name":"Tom Benzamin"})
> let dbRef = user.address
> db[dbRef.$ref].findOne({"_id":(dbRef.$id)})

{
   "_id": ObjectId("5bfabde76f280102ddf27969"),
   "band": "Slayer",
   "formation": { "year": ISODate("1982-01-01T00:00:00Z"), "city": "Los Angeles" },
   "popular_albums": [ "Show No Mercy!", "Seasons in the Abyss" ],
   "members": [ { "name": "Tom Araya", "instrument": "Bass/Vocals" }, ... ]
}

mysql> show tables;
 +-----------------------------------------+
 | Tables_in_sql                           |
 +-----------------------------------------+
 | bands                                   |
 | bands_members                           |
 | bands_popular_albums                    |
 +-----------------------------------------+
Note: Both arrays became new tables. Subdocuments are just flattened.