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})
>
沒有留言:
張貼留言