MongoDB Index

 explain is used to understand execution detail

In this case,"totalDocsExamined" : 1380558: shows "query by username" is quite insufficient
db.users.find({"username": "user101"}).explain("executionStats")
> db.users.find({"name":"user1"}).explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.users",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "name" : {
                                "$eq" : "user1"
                        }
                },
                "winningPlan" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "name" : {
                                        "$eq" : "user1"
                                }
                        },
                        "direction" : "forward"
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 4,
                "executionTimeMillis" : 767,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 1380558,
                "executionStages" : {
                        "stage" : "COLLSCAN",
                        "filter" : {
                                "name" : {
                                        "$eq" : "user1"
                                }
                        },
                        "nReturned" : 4,
                        "executionTimeMillisEstimate" : 75,
                        "works" : 1380560,
                        "advanced" : 4,
                        "needTime" : 1380555,
                        "needYield" : 0,
                        "saveState" : 1380,
                        "restoreState" : 1380,
                        "isEOF" : 1,
                        "direction" : "forward",
                        "docsExamined" : 1380558
                }
        },
        "serverInfo" : {
                "host" : "c38be4173edc",
                "port" : 27017,
                "version" : "4.4.1",
                "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
        },
        "ok" : 1
}


After createIndex on "name" attribute, query will be efficient
> db.users.createIndex({"name":1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}

// "totalDocsExamined" change from 1380558 to 4
> db.users.find({"name":"user1"}).explain("executionStats")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.users",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "name" : {
                                "$eq" : "user1"
                        }
                },
                "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "name" : 1
                                },
                                "indexName" : "name_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "name" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "name" : [
                                                "[\"user1\", \"user1\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 4,
                "executionTimeMillis" : 1,
                "totalKeysExamined" : 4,
                "totalDocsExamined" : 4,
                "executionStages" : {
                        "stage" : "FETCH",
                        "nReturned" : 4,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 5,
                        "advanced" : 4,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "docsExamined" : 4,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 4,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 5,
                                "advanced" : 4,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "keyPattern" : {
                                        "name" : 1
                                },
                                "indexName" : "name_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "name" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "name" : [
                                                "[\"user1\", \"user1\"]"
                                        ]
                                },
                                "keysExamined" : 4,
                                "seeks" : 1,
                                "dupsTested" : 0,
                                "dupsDropped" : 0
                        }
                }
        },
        "serverInfo" : {
                "host" : "c38be4173edc",
                "port" : 27017,
                "version" : "4.4.1",
                "gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
        },
        "ok" : 1
}


db.currentOp is used to check current operation
This command can be used to check internal status once createIndex spend too much time
> db.currentOp()
{
        "inprog" : [
                {
                        "type" : "op",
                        "host" : "c38be4173edc:27017",
                        "desc" : "WTJournalFlusher",
                        "active" : true,
                        "currentOpTime" : "2020-09-29T02:35:50.754+00:00",
                        "opid" : 19797,
                        "op" : "none",
                        "ns" : "",
                        "command" : {


                        },
                        "numYields" : 0,
                        "locks" : {


                        },
                        "waitingForLock" : false,
                        "lockStats" : {


                        },
                        "waitingForFlowControl" : false,
                        "flowControlStats" : {


                        }
                },
                {
                        "type" : "op",
                        "host" : "c38be4173edc:27017",
                        "desc" : "conn1",
                        "connectionId" : 1,
                        "client" : "127.0.0.1:39646",
                        "appName" : "MongoDB Shell",
                        "clientMetadata" : {
                                "application" : {
                                        "name" : "MongoDB Shell"
                                },
                                "driver" : {
                                        "name" : "MongoDB Internal Client",
                                        "version" : "4.4.1"
                                },
                                "os" : {
                                        "type" : "Linux",
                                        "name" : "Ubuntu",
                                        "architecture" : "x86_64",
                                        "version" : "18.04"
                                }
                        },
                        "active" : true,
                        "currentOpTime" : "2020-09-29T02:35:50.755+00:00",
                        "opid" : 19798,
                        "lsid" : {
                                "id" : UUID("7808c7a9-ca46-4d72-9657-dbda3b00dfab"),
                                "uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
                        },
                        "secs_running" : NumberLong(0),
                        "microsecs_running" : NumberLong(308),
                        "op" : "command",
                        "ns" : "admin.$cmd.aggregate",
                        "command" : {
                                "currentOp" : 1,
                                "lsid" : {
                                        "id" : UUID("7808c7a9-ca46-4d72-9657-dbda3b00dfab")
                                },
                                "$db" : "admin"
                        },
                        "numYields" : 0,
                        "locks" : {


                        },
                        "waitingForLock" : false,
                        "lockStats" : {


                        },
                        "waitingForFlowControl" : false,
                        "flowControlStats" : {


                        }
                },
                {
                        "type" : "op",
                        "host" : "c38be4173edc:27017",
                        "desc" : "WTCheckpointThread",
                        "active" : true,
                        "currentOpTime" : "2020-09-29T02:35:50.755+00:00",
                        "opid" : 19625,
                        "op" : "none",
                        "ns" : "",
                        "command" : {


                        },
                        "numYields" : 0,
                        "locks" : {


                        },
                        "waitingForLock" : false,
                        "lockStats" : {


                        },
                        "waitingForFlowControl" : false,
                        "flowControlStats" : {


                        }
                }
        ],
        "ok" : 1
}

Compound Index
// to optimize sort query
db.users.find().sort({“age”:1, “name”:1})

// need create index start from age
> db.users.createIndex({“age":1,”name":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 3,
    "numIndexesAfter" : 4,
    "ok" : 1
}








- 沒有留言:
標籤：

Query MongoDB

 

specify returned attributes
// find all with all attributes
> db.users.find().pretty()
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}

// find users with name:user1
> db.users.find({"name":"user1"}).pretty()
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}

// query all but only return name attribute
> db.users.find({},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }

// query all but only return name and phone attributes
> db.users.find({},{"name":1,"phone":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456", "1234567" ] }
>

// don't return _id
> db.users.find({},{"_id":0}).pretty()
{
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}

find >= or <= specified ages
// all data
> db.users.find().pretty()
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}
{
        "_id" : ObjectId("5f6ec687389de99e305f1af6"),
        "name" : "user2",
        "age" : 18
}
{
        "_id" : ObjectId("5f6ec68f389de99e305f1af7"),
        "name" : "user3",
        "age" : 20
}
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30
}
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40
}

// find users 18 <= age <= 30
> db.users.find({"age":{"$gte":18,"$lte":30}}).pretty()
{
        "_id" : ObjectId("5f6ec687389de99e305f1af6"),
        "name" : "user2",
        "age" : 18
}
{
        "_id" : ObjectId("5f6ec68f389de99e305f1af7"),
        "name" : "user3",
        "age" : 20
}
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30
}

// find users age >= 30
> db.users.find({"age":{"$gte":30}}).pretty()
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30
}
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40
}

// Search created after a specified id
> db.users.find({"_id":{"$gte":ObjectId("5f6ec68f389de99e305f1af7")}}).pretty()
{
        "_id" : ObjectId("5f6ec68f389de99e305f1af7"),
        "name" : "user3",
        "age" : 20
}
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30
}
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40
}

// set update date
> db.users.updateOne({"age":18},{"$set":{"updatedAt":new Date("01/01/2017")}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"age":20},{"$set":{"updatedAt":new Date("01/01/2018")}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"age":30},{"$set":{"updatedAt":new Date("01/01/2019")}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"age":40},{"$set":{"updatedAt":new Date("01/01/2020")}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}
{
        "_id" : ObjectId("5f6ec687389de99e305f1af6"),
        "name" : "user2",
        "age" : 18,
        "updatedAt" : ISODate("2017-01-01T00:00:00Z")
}
{
        "_id" : ObjectId("5f6ec68f389de99e305f1af7"),
        "name" : "user3",
        "age" : 20,
        "updatedAt" : ISODate("2018-01-01T00:00:00Z")
}
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30,
        "updatedAt" : ISODate("2019-01-01T00:00:00Z")
}
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40,
        "updatedAt" : ISODate("2020-01-01T00:00:00Z")
}

// find data after specified date
> db.users.find({"updatedAt": {"$gte": new Date("01/01/2019")}}).pretty()
{
        "_id" : ObjectId("5f6ec695389de99e305f1af8"),
        "name" : "user4",
        "age" : 30,
        "updatedAt" : ISODate("2019-01-01T00:00:00Z")
}
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40,
        "updatedAt" : ISODate("2020-01-01T00:00:00Z")
}

find data not equals to a specified value
// all user name
> db.users.find({},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

// find data which name is not user5 by $ne
> db.users.find({"name":{"$ne":"user5"}},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
>

Use $in and $nin to find multiple values
> db.users.find({},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

> db.users.find({"name":{"$in":["user1","user2"]}},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
>

// find not in user1 and user2
> db.users.find({"name":{"$nin":["user1","user2"]}},{"name":1})
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

Use $or to combine multiple criteria
> db.users.find({"$or":[{"age":40},{"name":"user3"}]})
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "age" : 20, "updatedAt" : ISODate("2018-01-01T00:00:00Z") }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5", "age" : 40, "updatedAt" : ISODate("2020-01-01T00:00:00Z") }
>

Use $not to find opposite criteria, notice $not need with a document argument
> db.users.find({"age":{"$not":{"$gte":40}}},{"name":1,"age":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "age" : 18 }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "age" : 20 }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4", "age" : 30 }
>

query null value
// update null value
> db.users.updateOne({"name":"user5"},{$set:{"nullvalue":null}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// query null value
> db.users.find({"nullvalue":null},{"name":1, "nullvalue":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5", "nullvalue" : null }
>

// set new attribute to one user
> db.users.updateOne({"name":"user3"},{$set:{"newatt":"newval"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// query newatt with null value can list all users without newatt attribute
> db.users.find({"newatt":null},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

// Query null value only when attribute exist by $exists
> db.users.find({"nullvalue":{"$eq":null, "$exists":true}},{"name":1})
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }
>

Query by regular expression
> db.users.find({"name":{$regex:/user+/}},{"name":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1" }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2" }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3" }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

> db.users.find({"name":{$regex:/.+5/}},{"name":1})
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }
>

Query value in array is same as query value in normal attribute
> db.users.find({"phone":"123456"},{"name":1,"phone":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456", "1234567" ] }
>

Query all matched values
// prepare data for user2 and user3
> db.users.updateOne({"name":"user2"},{"$push":{"phone":{$each:["123456","1234567","456789"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user3"},{"$push":{"phone":{$each:["123456","1234567","4567890"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// query all matched phone
> db.users.find({"phone":{$all:["123456","1234567"]}},{"name":1,"phone":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456", "1234567" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "123456", "1234567", "456789" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "123456", "1234567", "4567890" ] }

// query by user2 phones
> db.users.find({"phone":{$all:["123456","456789"]}},{"name":1,"phone":1})
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "123456", "1234567", "456789" ] }
>

$slice to query first N records
// list all phones
> db.users.find({},{"name":1, "phone":1})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456", "1234567" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "123456", "1234567", "456789" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "123456", "1234567", "4567890" ] }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }
>

// list top 2 phones
> db.users.find({},{"name":1, "phone":{$slice:2}})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456", "1234567" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "123456", "1234567" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "123456", "1234567" ] }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

// list top 1 phone
> db.users.find({},{"name":1, "phone":{$slice:1}})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "123456" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "123456" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "123456" ] }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

// list last 1 phone
> db.users.find({},{"name":1, "phone":{$slice:-1}})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "1234567" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "456789" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "4567890" ] }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

// list 2-3 phones
> db.users.find({},{"name":1, "phone":{$slice:[1,2]}})
{ "_id" : ObjectId("5f6a38748074fb4051b75cb8"), "name" : "user1", "phone" : [ "1234567" ] }
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "phone" : [ "1234567", "456789" ] }
{ "_id" : ObjectId("5f6ec68f389de99e305f1af7"), "name" : "user3", "phone" : [ "1234567", "4567890" ] }
{ "_id" : ObjectId("5f6ec695389de99e305f1af8"), "name" : "user4" }
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5" }

Query element value in specific range
// prepare data x
> db.users.updateOne({"name":"user1"},{"$push":{"x":{$each:[100,200,300]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user2"},{"$push":{"x":{$each:[200,300,400]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// query element (Wrong result)
> db.users.find({"x":{$lte:380,$gte:350}},{"name":1,"x":1})
{ "_id" : ObjectId("5f6ec687389de99e305f1af6"), "name" : "user2", "x" : [ 200, 300, 400 ] }

// Query by $elemMatch, expected result is empty because no match
> db.users.find({"x":{"$elemMatch":{$lte:380,$gte:350}}})
>

Query embedded document
// prepare data
> db.users.updateOne({"name":"user5"},{$set:{"nestName1":"nestVal1","nestName2":"nestVal2"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user5"},{$set:{"nest":{"nestName1":"nestVal1","nestName2":"nestVal2"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find({"name":"user5"}).pretty()
{
        "_id" : ObjectId("5f6ec69a389de99e305f1af9"),
        "name" : "user5",
        "age" : 40,
        "updatedAt" : ISODate("2020-01-01T00:00:00Z"),
        "nullvalue" : null,
        "nestName1" : "nestVal1",
        "nestName2" : "nestVal2",
        "nest" : {
                "nestName1" : "nestVal1",
                "nestName2" : "nestVal2",
                "nestName3" : "nestVal3"
        }
}

// find by nestName1 and nestName2 by nest.nestName1 and nest.nestName2
> db.users.find({"nest.nestName1":"nestVal1","nestName2":"nestVal2"},{"name":1,"nest":1})
{ "_id" : ObjectId("5f6ec69a389de99e305f1af9"), "name" : "user5", "nest" : { "nestName1" : "nestVal1", "nestName2" : "nestVal2", "nestName3" : "nestVal3" } }

// Without dot(.) to query, embedded document need fully match
> db.users.find({"nest":{"nestName1":"nestVal1"}},{"name":1,"nest":1})
>
- 沒有留言:
標籤：

MongoDB more Update

Insert many
  • Send multiple data in one request will be faster than insert one by one
  • Command line tool mongoimport can be used to import data instead of insertMany many times
  • Message size can’t be larger than 48 MB
> db.movies.insertMany([{"title":"Movie 1"},{"title":"Movie 2"},{"title":"Movie 3"}])
{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("5f6876732fbc27989eb7f4c2"),
        ObjectId("5f6876732fbc27989eb7f4c3"),
        ObjectId("5f6876732fbc27989eb7f4c4")
    ]
}

Ordered and unordered insert
Ordered insert (Default): MongoDB stop insert later records whenever any data insert fail
Unordered insert: MongoDB will try to insert all data regardless some data fail, unordered insert will have better performance

drop 
ex. db.movies.drop()
> db.movies.insertOne(movie2)
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f68e75e85d32460c3bfa6e3")
}
> db.movies.find().pretty()
{
        "_id" : ObjectId("5f68e75885d32460c3bfa6e2"),
        "title" : "A movie",
        "director" : "A director",
        "year" : 2020
}
{
        "_id" : ObjectId("5f68e75e85d32460c3bfa6e3"),
        "title" : "A movie 2",
        "director" : "A director 2",
        "year" : 2020
}
> db.movies.drop()
true
> db.movies.find().pretty()

update
It seems update is an atomic operation, but I'm curious what will happen when two update operations happen at the same time, and the second operation filter criteria is not matched because the first operation modified the data?
> db.movies.find().pretty()
{
        "_id" : ObjectId("5f6950a694d2d7702605f470"),
        "title" : "A movie",
        "director" : "A director",
        "year" : 2020
}
{
        "_id" : ObjectId("5f6950a794d2d7702605f471"),
        "title" : "A movie 2",
        "director" : "A director 2",
        "year" : 2020
}

// Update one movie
> db.movies.updateOne({"title":"A movie"},{$set:{"year":2021,"review":0}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.movies.find({"title":"A movie"}).pretty()
{
        "_id" : ObjectId("5f6950a694d2d7702605f470"),
        "title" : "A movie",
        "director" : "A director",
        "year" : 2021,
        "review" : 0
}

replace
replace command will replace whole document
> db.movies.replaceOne({"title":"A movie"}, {"title":"B movie", "year":1990})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.movies.find().pretty()
{
        "_id" : ObjectId("5f6950a694d2d7702605f470"),
        "title" : "B movie",
        "year" : 1990
}
{
        "_id" : ObjectId("5f6950a794d2d7702605f471"),
        "title" : "A movie 2",
        "director" : "A director 2",
        "year" : 2020
}

increment a number
// create page document
> pageA= {"page":"page A", "view":0}
{ "page" : "page A", "view" : 0 }
> db.page.insertOne(pageA)
{
        "acknowledged" : true,
        "insertedId" : ObjectId("5f69551d94d2d7702605f472")
}

// check data
> db.page.find({"page":"page A"}).pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "view" : 0
}

// increment
> db.page.updateOne({"page":"page A"},{"$inc":{"view":1}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// check result
> db.page.find({"page":"page A"}).pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "view" : 1
}

$unset
// check there is a view property
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "view" : 1
}

// unset view property
> db.page.updateOne({"page":"page A"},{"$unset":{"view":1}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// check view property disappeared
> db.page.find().pretty()
{ "_id" : ObjectId("5f69551d94d2d7702605f472"), "page" : "page A" }

$set change attribute value in embedded document
// set new property: user
> db.page.updateOne({"page":"page A"}, {"$set":{
...    "user":{
...      "display_name":"",
...      "devices":[
...
...      ],
...      "subscriptions":{
...          "vpn":{
...            "active":true,
...          }
...      },
...      "max_devices":5
...    },
... }})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// check data
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "user" : {
                "display_name" : "",
                "devices" : [ ],
                "subscriptions" : {
                        "vpn" : {
                                "active" : true
                        }
                },
                "max_devices" : 5
        }
}

// change display_name
> db.page.updateOne({"page":"page A"},{"$set":{"user.display_name":"ABCDE"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// check update result
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "user" : {
                "display_name" : "ABCDE",
                "devices" : [ ],
                "subscriptions" : {
                        "vpn" : {
                                "active" : true
                        }
                },
                "max_devices" : 5
        }
}

$push data to array attribute
// push data to array attribute
> db.page.updateOne({"page":"page A"},{"$push":{"comment":"hello"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.page.updateOne({"page":"page A"},{"$push":{"comment":"hello2"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// check result
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "comment" : [
                "hello",
                "hello2"
        ]
}

$push multiple elements to an array attribute by $each
// Can't add multiple elements to an array by simply $push
> db.page.updateOne({"page":"page A"},{"$push":{"comment":["hello3","hello4"]}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// Because only $push will add only one element, thus one element with array value will be added
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "comment" : [
                "hello",
                "hello2",
                [
                        "hello3",
                        "hello4"
                ]
        ]
}

// Need $each to add multiple elements
> db.page.updateOne({"page":"page A"},{"$push":{"comment":{"$each":["hello3","hello4"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "comment" : [
                "hello",
                "hello2",
                [
                        "hello3",
                        "hello4"
                ],
                "hello3",
                "hello4"
        ]
}

$slice array to 3 elements when performing $each
> db.page.updateOne({"page":"page A"},{"$push":{"comment":{"$each":[],"$slice":-3}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

// rest 3 records
> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "comment" : [
                [
                        "hello3",
                        "hello4"
                ],
                "hello3",
                "hello4"
        ]
}


$sort when $slice
> db.page.updateOne({"page":"page A"},{"$push":{"top3":{"$each":[{"name":"A","height":5},{"name":"B","height":4},{"name":"C","height":3},{"name":"D","height":2},{"name":"E","height":1}],"$slice":-3,"$sort":{"height":1}}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.page.find().pretty()
{
        "_id" : ObjectId("5f69551d94d2d7702605f472"),
        "page" : "page A",
        "comment" : [
                "hello3",
                "hello4"
        ],
        "top3" : [
                {
                        "name" : "C",
                        "height" : 3
                },
                {
                        "name" : "B",
                        "height" : 4
                },
                {
                        "name" : "A",
                        "height" : 5
                }
        ]
}

$push comment
> db.users.updateOne({"name":"user1"},{"$push":{"comment":{"email":"a@gmail.com"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.updateOne({"name":"user1"},{"$push":{"comment":{"email":"b@gmail.com"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.find().pretty()
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ]
}

$push and then $addToSet phone with $each
> user = {"name":"user 1"}
{ "name" : "user 1" }
> db.users.insertOne(user)
{
    "acknowledged" : true,
    "insertedId" : ObjectId("5f6c46393c90a681c72b7df5")
}

> db.users.updateOne({"name":"user 1"},{$push:{"phone":"123456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "123456"
    ]
}

// add existing phone, no modification
> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": "123456"}})
{ "acknowledged" : true, “matchedCount" : 1, "modifiedCount" : 0 }

// add new phone
> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": "223456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "123456",
        "223456"
    ]
}

// add existing phone, no modification
> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": "223456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 0 }

// add multiple elements by $each, one non-existing phone, so one modification
> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": {"$each":["223456","12345"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": {"$each":["223456","12345","123456"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 0 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "123456",
        "223456",
        "12345"
    ]
}


Remove element by $pop, $pop 1 remove from end, $pop -1 remove from beginning
> db.users.updateOne({"name":"user 1"}, {"$pop":{"phone": 1}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "123456",
        "223456"
    ]
}

> db.users.updateOne({"name":"user 1"}, {"$pop":{"phone": -1}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456"
    ]
}

Remove element by $pull
// pull element but value is not matched, no modification
> db.users.updateOne({"name":"user 1"}, {"$pull":{"phone":"123456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 0 }

//
> db.users.updateOne({"name":"user 1"}, {"$pull":{"phone":"223456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [ ]
}

Remove multiple elements by $pull
// push elements
> db.users.updateOne({"name":"user 1"}, {"$addToSet":{"phone": {"$each":["223456","12345","123456"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "123456"
    ]
}
> db.users.updateOne({"name":"user 1"}, {$push:{"phone": {"$each":["223456","12345","123456"]}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "123456",
        "223456",
        "12345",
        "123456"
    ]
}

// pull 123456, all 123456 will be removed
> db.users.updateOne({"name":"user 1"}, {"$pull":{"phone":"123456"}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ]
}

$inc increase value in specified index array
// original data
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ]
}

// push 2 comments
> db.users.updateOne({"name":"user 1"}, {$push:{"comment":{"vote":0,"message":"haha"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user 1"}, {$push:{"comment":{"vote":0,"message":"haha 2"}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 0,
            "message" : "haha"
        },
        {
            "vote" : 0,
            "message" : "haha 2"
        }
    ]
}

// increase the first element vote
> db.users.updateOne({"name":"user 1"}, {"$inc":{"comment.0.vote":1}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "haha"
        },
        {
            "vote" : 0,
            "message" : "haha 2"
        }
    ]
}

// increase 100 to the second element
> db.users.updateOne({"name":"user 1"}, {"$inc":{"comment.1.vote":100}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "haha"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ]
}

// update message when array element match arrayFilters
> db.users.updateOne({"name":"user 1"}, {$set:{"comment.$[elmt].message":"Good"}},{arrayFilters:[{"elmt.vote":{$lte: 11}}]})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }

> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "Good"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ]
}

upsert: update when exist, otherwise, insert
// upsert age to user 1
> db.users.updateOne({"name":"user 1"},{"$set":{"age":10}},{"upsert":true})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "Good"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ],
    "age" : 10
}

// upsert age to user 2
> db.users.updateOne({"name":"user 2"},{"$set":{"age":10}},{"upsert":true})
{
    "acknowledged" : true,
    "matchedCount" : 0,
    "modifiedCount" : 0,
    "upsertedId" : ObjectId("5f6d6fa0d8c8c1bae1785718")
}
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "Good"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ],
    "age" : 10
}
{
    "_id" : ObjectId("5f6d6fa0d8c8c1bae1785718"),
    "name" : "user 2",
    "age" : 10
}

$setOnInsert to config attribute when insert
// set createdAt when insert
> db.users.updateOne({"name":"user 2"},{"$setOnInsert":{"createdAt":new Date()}},{"upsert":true})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 0 }
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "Good"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ],
    "age" : 10
}
{
    "_id" : ObjectId("5f6d6fa0d8c8c1bae1785718"),
    "name" : "user 2",
    "age" : 10
}

// 
> db.users.updateOne({"name":"user 3"},{"$setOnInsert":{"createdAt":new Date()}},{"upsert":true})
{
    "acknowledged" : true,
    "matchedCount" : 0,
    "modifiedCount" : 0,
    "upsertedId" : ObjectId("5f6d738ed8c8c1bae1785730")
}
> db.users.find().pretty()
{
    "_id" : ObjectId("5f6c46393c90a681c72b7df5"),
    "name" : "user 1",
    "phone" : [
        "223456",
        "12345",
        "223456",
        "12345"
    ],
    "comment" : [
        {
            "vote" : 1,
            "message" : "Good"
        },
        {
            "vote" : 100,
            "message" : "haha 2"
        }
    ],
    "age" : 10
}
{
    "_id" : ObjectId("5f6d6fa0d8c8c1bae1785718"),
    "name" : "user 2",
    "age" : 10
}
{
    "_id" : ObjectId("5f6d738ed8c8c1bae1785730"),
    "name" : "user 3",
    "createdAt" : ISODate("2020-09-25T04:35:26.398Z")
}

findOneAndUpdate can handle race condition, but it return old document by default. 
Can returnNewDocument to return the one after modification
// update and return old document
> db.users.findOneAndUpdate({"name":"user1"},{"$push":{"phone":{"$each":["123456"]}}})
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ]
}

// update and return new document
> db.users.findOneAndUpdate({"name":"user1"},{"$push":{"phone":{"$each":["1234567"]}}},{"returnNewDocument":true})
{
        "_id" : ObjectId("5f6a38748074fb4051b75cb8"),
        "name" : "user1",
        "comment" : [
                {
                        "email" : "a@gmail.com"
                },
                {
                        "email" : "b@gmail.com"
                }
        ],
        "phone" : [
                "123456",
                "1234567"
        ]
}




- 沒有留言:
標籤：
訂閱： 文章 (Atom)

別名演算法 Alias Method

 題目 每個伺服器支援不同的 TPM (transaction per minute) 當 request 來的時候, 系統需要馬上根據 TPM 的能力隨機找到一個適合的 server. 雖然稱為 "隨機", 但還是需要有 TPM 作為權重. 解法 別名演算法...