MongoDB查詢內嵌文件

weixin_34107955發表於2018-08-27
MongoDB

一、概述

   有兩種方法可以查詢內嵌文件：查詢整個文件；針對鍵值對進行查詢。這兩種方式是不同的，下面我通過例子進行分別說明。

二、查詢整個文件

例如:有如下文件

class GoalReportMongoModel(Document):

    id = StringField(primary_key=True)
    user_id = StringField()
    date = StringField()
    update_time = LongField()
    goals = ListField()
    leader_id = StringField()
    comment = StringField()
    checked = BooleanField()
    check_time = LongField()

參考示例：查詢date 為2016-08-05 user_id 為U06UGFL12
可以這樣查詢

db.getCollection("goal_report").find({date:'2016-08-05',user_id:'U06UGFL12'})

這種查詢會去精確匹配整個內嵌文件

三、鍵值對查詢

我們一般在查詢時，不會去匹配整個內嵌文件，通常只針對內嵌文件的特定鍵值去查詢。怎麼做？

答：查詢文件時，可以使用"."來表示進入內嵌文件。

參考例項：

 db.getCollection("goal_report").find({"goals.goal":'價值目標COO-1',"goals.assigner":'san.zhang'})

查詢結果

{ 
    "_id" : "U06UGFL12_2016-08-05", 
    "user_id" : "U06UGFL12", 
    "date" : "2016-08-05", 
    "update_time" : NumberLong(1470397732), 
    "goals" : [
        {
            "status" : "ensure", 
            "criterion_ok" : true, 
            "user_id" : "U06UGFL12", 
            "goal" : "價值目標COO-1", 
            "assigner" : "san.zhang", 
            "tool" : "", 
            "method_ok" : true, 
            "done_time" : NumberInt(0), 
            "method" : "", 
            "reason" : "本週",
            "criterion" : "", 
            "goal_type" : "valuable", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(2), 
            "id" : NumberInt(1135241564), 
            "planh" : ""
        }, 
       {
            "status" : "ensure", 
            "criterion_ok" : true, 
            "user_id" : "U06UGF232", 
            "goal" : "價值目標COO-2", 
            "assigner" : "san.zhang", 
            "tool" : "", 
            "method_ok" : true, 
            "done_time" : NumberInt(0), 
            "method" : "", 
            "reason" : "本週",
            "criterion" : "", 
            "goal_type" : "valuable", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(2), 
            "id" : NumberInt(1135241564), 
            "planh" : ""
        }, 

    ], 
    "leader_id" : "U08EC7FTM", 
    "checked" : true
}

四、陣列裡麵包含內嵌文件的查詢

這種查詢相對來說比較複雜一點，所以內嵌文件的匹配也需要有些技巧。例如下面的部落格文件中有一個commens:鍵用來儲存別人的評論資訊。

db.blog.insert({  
    "_id":"B001",  
    "title":"MongoDB查詢",  
    "comments":[  
      {"name":"ickes","score":3,"comment":"nice"},  
      {"name":"xl","score":4,"comment":"nice"},  
      {"name":"eksliang","score":5,"comment":"nice"},  
      {"name":"ickes","score":6,"comment":"nice"}  
    ]  
})

現在要查詢由ickes評論的且5分以上文章

- 不能使用db.blog.find({"comments":{"name":"ickes","score":{"$gt":5}})去查，因為內嵌文件的匹配是精確匹配，必須要匹配完整的文件，而這個查詢不會匹配comment鍵

-  不能使用db.blog.find({"comments":{"name":"ickes","score":{ '$gt' : 5},"comment":"nice"}})去查，還是那句話，文件的匹配時精確匹配，這裡使用了$gt作為範圍，所以也查不到

- 不能使用db.blog.find({"comments.name":"ickes","comments.score":{"$gt":5}})去查，前面講查詢條件的時候說過，查詢條件裡面的鍵值對會解釋為AND,但是對於陣列的內嵌文件他會解釋為OR的關係，也就是說上面實際是這樣的comments.name:ickes或者comments.score":{"$gt":5},這明顯不行嗎！（注意如果內嵌文件不在陣列中，還是AND，所以我才把這個拿出來單獨討論）

那對於陣列裡面的內嵌文件到底怎麼辦？應該這麼辦，如下所示

這裡需要使用"$elemMatch"操作符，僅當這種時候才使用這個操作符

db.blog.find({"comments":{  
    "$elemMatch":{"name":"ickes","score":{"$gt":5}}  
}})

五、返回與查詢條件相匹配的任意一個陣列元素

   我們可以使用"$slice"操作符進行陣列元素返回限制，但是當陣列裡面儲存的是文件的時候，我就想返回與我查詢條件相匹配的那個元素，其他的不要，怎麼做？有技巧的哦！

文件結構如下：

db.blog.insert({  
    "_id":"B001",  
    "title":"MongoDB查詢",  
    "comments":[  
      {"name":"ickes","score":3,"comment":"nice"},  
      {"name":"xl","score":4,"comment":"nice"},  
      {"name":"eksliang","score":5,"comment":"nice"},  
      {"name":"ickes","score":6,"comment":"nice"}  
    ]  
})

參考例項：

db.blog.find({"comments":{  
   "$elemMatch":{"name":"ickes","score":{"$gt":5}}}},  
   {"comments.$":1}--第二個引數是限制返回資料的，別看錯了，這是第二個引數  
)

返回結果如下：僅返回與當前查詢條件相匹配的那個內嵌文件。

{  
  "_id" : "B001",   
  "comments" : [ { "name" : "ickes", "score" : 6, "comment" : "nice" } ]   
}

如果當前查詢有多個內嵌文件匹配，只會返回第一個

六、按照正規表示式查詢內嵌文件某個欄位包含某個字串

舉例：

{ 
    "_id" : "U08G7H48Y_2016-07-08", 
    "user_id" : "U08G7H48Y", 
    "date" : "2016-07-08", 
    "update_time" : NumberLong(1467978750), 
    "goals" : [
        {
            "status" : "unsure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保IA專案組盈利指標達標", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : true, 
            "id" : NumberInt(1363999581), 
            "done_time" : NumberInt(0), 
            "reason" : "短期目標不能確保：找不到更好的拿新使用者的方法", 
            "create_time" : NumberLong(1466508766869), 
            "criterion" : "確保每天收入達到5w，力爭6w\nFollow：1w\nLike：1.5w\nTracker：0.5w\nPrivacy：1.5w\nMemoryBoost：1.5w", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(2), 
            "method" : "已經入思維導圖。\n確保7月30日收入達到1.5w", 
            "planh" : ""
        }, 
        {
            "status" : "ensure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保給組員製造W-W局面", 
            "assigner" : "", 
            "tool" : "Wunderlist", 
            "method_ok" : true, 
            "id" : NumberLong(2633899071), 
            "done_time" : NumberInt(0), 
            "reason" : "", 
            "create_time" : NumberLong(1465283369329), 
            "criterion" : "幫助管理者（李丁，時遷，賈博，盧燕濤，孟友陽）盯住他的組員", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(1), 
            "method" : "確保增加專案負責人的管理組員能力：\n李丁每兩週跟我Review一下他的組員：需要設定李丁的ToDo\n時遷每兩週跟我Review一下他的組員：需要設定時遷的ToDo\n盧燕濤每兩週跟我Review一下他的組員：需要設定盧燕濤的ToDo\n孟友陽每週跟我Review一下他的組員：需要設定孟友陽的ToDo\n\n確保跟每個PM每個季度至少聊天一次: 已設定Review聊天記錄的todo", 
            "planh" : ""
        }, 
        {
            "status" : "unsure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保PM可以流動起來", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : true, 
            "id" : NumberInt(602193464), 
            "done_time" : NumberInt(0), 
            "reason" : "最近入職PM比較難，感覺流動這個目標會看不住", 
            "create_time" : NumberLong(1465284005711), 
            "criterion" : "確保組內PM新老搭配合理\n確保組內新PM快速培訓", 
            "expire_time" : NumberInt(0), 
            "execution" : 1.5, 
            "method" : "確保7月底之前輸入兩個pm", 
            "planh" : ""
        }, 
        {
            "status" : "ensure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保專案組流程完善", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : true, 
            "id" : NumberInt(1616685108), 
            "done_time" : NumberInt(0), 
            "reason" : "", 
            "create_time" : NumberLong(1465284199829), 
            "criterion" : "確保專案組DEV流程完善", 
            "expire_time" : NumberInt(0), 
            "execution" : 1.5, 
            "method" : "確保專案組DEV流程完善", 
            "planh" : ""
        }, 
        {
            "status" : "unsure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保專案組執行力達到1X", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : false, 
            "id" : NumberLong(4283629091), 
            "done_time" : NumberInt(0), 
            "reason" : "暫時沒有找到好的方法確保這個目標", 
            "create_time" : NumberLong(1465284143431), 
            "criterion" : "MB每週提交一個版本\nPM每週提交一個版本\niOS每兩完成一個Feature", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(2), 
            "method" : "每個單子Archive的時候要想一些是否需要rethink", 
            "planh" : ""
        }, 
        {
            "status" : "ensure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "價值目標：確保盯住競爭對手的程式", 
            "assigner" : "", 
            "tool" : "Wunderlist", 
            "method_ok" : true, 
            "id" : NumberLong(3852562557), 
            "done_time" : NumberInt(0), 
            "reason" : "", 
            "create_time" : NumberLong(1465283982245), 
            "criterion" : "確保看榜單，Like，Tracker等競品", 
            "expire_time" : NumberInt(0), 
            "execution" : 1.5, 
            "method" : "確保每", 
            "planh" : ""
        }, 
        {
            "status" : "ensure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "焦點目標", 
            "assigner" : "", 
            "tool" : "Wunderlist", 
            "method_ok" : true, 
            "id" : NumberInt(1973727620), 
            "done_time" : NumberInt(0), 
            "reason" : "", 
            "create_time" : NumberLong(1465284255588), 
            "criterion" : "確保關鍵字推廣在持續推進", 
            "expire_time" : NumberInt(1469808000), 
            "execution" : 1.5, 
            "method" : "推廣ROI", 
            "planh" : ""
        }, 
        {
            "status" : "unsure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "焦點目標：確保", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : true, 
            "id" : NumberInt(123662851), 
            "done_time" : NumberInt(0), 
            "reason" : "這週上線的時候。", 
            "create_time" : NumberLong(1465278975537), 
            "criterion" : "交叉", 
            "expire_time" : NumberInt(1467216000), 
            "execution" : NumberInt(2), 
            "method" : "詳見思維導圖", 
            "planh" : ""
        }, 
        {
            "status" : "ensure", 
            "user_id" : "U08G7H48Y", 
            "goal" : "焦點目標：確保跟進產品需求，多跟PM討論各種產品需求", 
            "assigner" : "", 
            "tool" : "", 
            "method_ok" : true, 
            "id" : NumberLong(3918152115), 
            "done_time" : NumberInt(0), 
            "reason" : "", 
            "create_time" : NumberLong(1467335980569), 
            "criterion" : "確保每天至少找一個PM討論需求15分鐘\n確保每次產品需求評審會都參加", 
            "expire_time" : NumberInt(0), 
            "execution" : NumberInt(2), 
            "method" : "每天至少找一個PM討論需求15分鐘，已經入todo\n", 
            "planh" : ""
        }
    ], 
    "leader_id" : "U06UGFL12", 
    "comment" : "個人產品能力和", 
    "check_time" : NumberLong(1468158420)
}

mongo 原生程式碼查詢

db.getCollection("goal_report").find({"goals":{"$elemMatch":{"goal":{"$regex":/價值目標/}}}})

mongoengine 查詢

GoalReportMongoModel.objects(__raw__={"goals":{"$elemMatch":{"goal":{"$regex":'價值目標'}}}})

七、 查詢陣列

陣列元素模糊匹配

陣列欄位badges每個包含該元素black的文件都將被返回

db.users.find({badges:"black"},{"_id":1,badges:1})
# 結果
        { "_id" : 1, "badges" : [ "blue", "black" ] }
        { "_id" : 4, "badges" : [ "red", "black" ] }
        { "_id" : 6, "badges" : [ "black", "blue" ] }
陣列元素精確(全)匹配

陣列欄位badges的值為["black","blue"]的文件才能被返回(陣列元素值和元素順序全匹配)

 db.users.find({badges:["black","blue"]},{"_id":1,badges:1})
#結果
        { "_id" : 6, "badges" : [ "black", "blue" ] }
陣列內嵌文件查詢

查詢陣列points內嵌文件鍵points的值小於等於55的文件，此處通過.成員的方式實現

db.users.find( { 'points.points': { $lte: 55}},{"_id":1,points:1})
# 結果
        { "_id" : 3, "points" : [ { "points" : 81, "bonus" : 8 }, { "points" : 55, "bonus" : 20 } ] }
        { "_id" : 4, "points" : [ { "points" : 53, "bonus" : 15 }, { "points" : 51, "bonus" : 15 } ] }

相關文章