Mongo Instructions

Install

python

pip install pymongo

import pymongo

myclient = pymongo.MongoClient("mongodb://localhost:27017/")
mydb = myclient["runoobdb"]
mycol = mydb["sites"]
x = mycol.find_one()
print(x)

Hint

Snippts

• system

mongod --version // show version.
db.help() // show commands.
db.stats()  // show collections and parameters.
show dbs // db list.
db.dropDatabase() //drop db.
user [db]
db.createCollection("[name]")

db.[coll_name].drop()  // drop collection

• create

db.[coll_name].insertOne()
db.[coll_name].insertMany()

• query basic

collectionName.distinct(field)  //unique or distince.

• query advance

db.getCollection('db').find({"code": "C261", "name": "C"}).sort({"report_time":-1})  // 倒叙

db.getCollection('db').find({"code": "C261", "name": "C"}).sort({"report_time":-1})  // 正叙

// 4.2+
// replace one field with another multi columns.
db.getCollection('test_collection').update({}, [
        {"$set": {"name": { "$concat": ["$name2", " ", "$name1"]}}}
    ])

// pagination with aggregate.
db.getCollection('temp').aggregate(
        [{"$match":{"$or":[{"_id":{"$gt":"1005"}},{"_id":{"$eq":"1001"}}]}},
          {"$project":{"_id":0,"name":1}},
          {"$sort":{"name":1}},
          {"$facet":{
            "data":[{"$skip":10},{"$limit":10}],
            "pagination":[{"$count":"total"}]}
          }])

// filter selected fields with regex and return.  V4.2+
db.getCollection('temp').aggregate([
   {"$match":{"_id":{"$lt":"1005"}}},
   {
       "$addFields": { "data": { "$objectToArray": "$$ROOT" }}
   },
   {
      "$project": { "items": { "$filter": {
               "input": "$data",
               "as": "item",
               "cond": {
                  "$regexMatch": { "input": "$$item.k", "regex":  "a" }
               }
            } } }
   },
   { "$replaceRoot":
      { "newRoot": {
         "$mergeObjects": [
            {"_id": "$_id"},
            { "$arrayToObject": "$items" }]
       } }
    }
])
// alowdisk
db.stocks.aggregate( [
      { $sort : { cusip : 1, date: 1 } }
   ],
   { allowDiskUse: true }
)

// 查询，从数组中查询，然后分组，创建树状结构。
db.es_dimension_data.aggregate(
        [{"$match":{"index": "du-test"}},
        {"$project":{
            "first": { "$arrayElemAt": [ "$list", 0 ] },
            "last": { "$arrayElemAt": [ "$list", -1 ] }
        }},
        {
            "$group": {
                "_id": "$first",
                "tags": {"$addToSet": "$last"}
            }
        },
        {
            "$project":{
                "dimension": "$_id",
                "tags": "$tags"
            }
        }])
// mongo group by date and count
db.task_results.aggregate([
    {$addFields: {"date": {"$toDate": "$date_done"}}},
    {"$group": {
        _id : {$dateToString: {format: "%Y-%m-%d", date: "$date"}},
        count: {$sum: 1}
    }},
    {
        $sort : { _id: -1 }
    }
    ])

// aggregation pipeline
b.collection_name.aggregate([
   //First stage
   { $match: { status: "" } },
   //Second stage
   { $group: { _id: "$cust_id", total: { $sum: "$amount" } } },
   //Third Stage
    { $sort : {sort_field: -1 }}
// group by multiple fields.
db.student.aggregate([ {$group: {_id: {name:"$name",
                                         timestamp:"$timestamp" }}},
                                        {$count:"timestamp"}
                      ])

// group and count
db.student.aggregate([
             {$match:{age: {$gte: 25}}},
             {$group: {_id: {name:"$name", age:"$age"}}},
             {$count: "nameAndAge"}
])
// update and parse

db.LR_TH_COMPANY_dwb.find({}).forEach(function updateOne(item){
    var src = item.tags;
    var tags_obj = JSON.parse(src);
    const tags_list = Object.keys(tags_obj);
    print(tags_list.length);
    db.LR_TH_COMPANY_dwb.update(
        { _id: item._id },
        { "$set": { "tagsList": tags_list } }
    );
});

// explain
db.test.find({"tag": {"$in": "t"}}).explain("executionStats")

• update

// rename collection
collectionName.renameCollection(newCollectionName)

// update one field.
db.collection.update({"_id": "123"}, {"$set": {"field": "value"}})

// replace document.
db.restaurant.replaceOne(
      { "name" : "Central Perk Cafe" },
      { "name" : "Central Pork Cafe", "Borough" : "Manhattan" }
   )
// update and set the field with one field split to array.

db.mongo_dimension_data.find({}).forEach(function updateOne(item){
    var src = item.src;
    var src_list = item.src.split("_");
    print(src_list);
    db.mongo_dimension_data.update(
        { _id: item._id },
        { "$set": { "list": src_list } }
    );
});

• snippts

  • cursor恢复未执行状态

    #!/usr/bin/python3
    
    from pymongo import MongoClient
    client = MongoClient('mongodb://localhost:27017/')
    with client:
        db = client.testdb
        cars = db.cars.find()
        print(cars.next())
        print(cars.next())
        print(cars.next())
        cars.rewind()  # 恢复到unevaluate 的状态
    
        print(cars.next())
        print(cars.next())
        print(cars.next())
    
        print(list(cars))
    

• index

// create index.
collectionName.createIndex({Key:1})
// show index.
collectionName.getIndexes()
// drop index.
collectionName.dropIndex({key})

• db

# backup db.
mongodump --db dbName --out outFile --host "IP:PORT"  --username <user>  --password <pass>

# restore
mongorestore --db newDB "pathOfOldBackup"

• collection

# export.
mongoexport --db dbName --collection collectionName --out outputFile
# export field.
mongoexport --db dbName --collection collectionName --out outputFile --fields fieldname
# import
mongoimport --db dbName --collection collectionName --file inputFile

• query recent slow query

function showMongoLog({filter}){
    function getMostRecent1024MongodEventLog() {
        if (parseFloat(db.version()) < 3.0) throw new Error("Require MongoDB 3.0 or above")
        const logFilter = "global"; //possible value:  global|rs|startupWarnings
        const res = db.adminCommand({ getLog: logFilter });

        if (!res.ok) throw res;
        return res.log;
    }

    const logLines=getMostRecent1024MongodEventLog();

    const logEntries=mb.parseMongoDBLog(logLines)
        .filter(filter)

    return logEntries
}

const filter=it => true
    //&& moment(it.timestamp).isBetween("2022-08-11 07:40","2022-08-12 07:40") //The match is exclusive.
    //&& /command.*aggregate/.test(it.message) //test message
    //&& (["Debug","Info","Warning","Error","Fatal"].indexOf(it.severity)>-1)  //severity
    //&& it.component==="COMMAND" //component, COMMAND,INDEX,QUERY,REPL,WRITE.  use db.getLogComponents() to get all
    && it.duration_ms >= 200

showMongoLog({filter})

Problems

• count

  count slow when the result is big number.