MongoDB（13）- 查詢操作返回指定的欄位

小菠蘿測試筆記發表於2021-06-02
MongoDB

插入測試資料

db.inventory.insertMany( [
  { item: "journal", status: "A", size: { h: 14, w: 21, uom: "cm" }, instock: [ { warehouse: "A", qty: 5 } ] },
  { item: "notebook", status: "A",  size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "C", qty: 5 } ] },
  { item: "paper", status: "D", size: { h: 8.5, w: 11, uom: "in" }, instock: [ { warehouse: "A", qty: 60 } ] },
  { item: "planner", status: "D", size: { h: 22.85, w: 30, uom: "cm" }, instock: [ { warehouse: "A", qty: 40 } ] },
  { item: "postcard", status: "A", size: { h: 10, w: 15.25, uom: "cm" }, instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
]);

後面的栗子都會用到這裡的測試資料

 

查詢到的文件會返回所有欄位

> db.inventory.find( { status: "A" } )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "status" : "A", "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ] }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "status" : "A", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "status" : "A", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] } 

預設情況下，會返回文件的所有欄位

 

等價 SQL 的寫法

SELECT * from inventory WHERE status = "A"

 

複習下 find() 的語法格式

db.collection.find(query, projection)
  • query：可選項，設定查詢操作符指定查詢條件
  • projection ：可選項，指定要在與 query 匹配的文件中返回的欄位，如果忽略此選項則返回所有欄位【本節重點】

 

僅返回指定的欄位和 _id

> db.inventory.find( { status: "A" }, { item: 1, status: 1 } )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "status" : "A" }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "status" : "A" }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "status" : "A" }
  • 即使不指定 _id 也會自動返回
  • 需要返回的欄位只需要欄位值寫 1 就行  { <field>: 1 } 

 

等價 SQL 的寫法

SELECT _id, item, status from inventory WHERE status = "A"

 

返回所有欄位但排除指定欄位

> db.inventory.find( { status: "A" }, { status: 0, instock: 0 } )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "size" : { "h" : 14, "w" : 21, "uom" : "cm" } }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" } }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" } }

不需要返回的欄位只需要欄位值填 0 就行 { <field>: 0 }  

 

如果 _id ：0 會不返回 _id 欄位嗎？

> db.inventory.find( { status: "A" }, { status: 0, instock: 0,_id : 0 } )
{ "item" : "journal", "size" : { "h" : 14, "w" : 21, "uom" : "cm" } }
{ "item" : "notebook", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" } }
{ "item" : "postcard", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" } }

答案是會的

 

能不能同時指定包含一些欄位，不包含一些欄位呢？

> db.inventory.find( { status: "A" }, { status: 0, instock: 1} )
Error: error: {
    "ok" : 0,
    "errmsg" : "Cannot do inclusion on field instock in exclusion projection",
    "code" : 31253,
    "codeName" : "Location31253"
}

答案是不能的，如果想指定 <projection> 是包含欄位，那所有欄位值都得統一是 1，相反如果是不包含，也必須都是 0

 

那 _id 欄位有這個限制嗎？

_id 欄位同時傳了 0 和 1

> db.inventory.find( { status: "A" }, { _id : 1,_id : 0 } )
{ "item" : "journal", "status" : "A", "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "instock" : [ { "warehouse" : "A", "qty" : 5 } ] }
{ "item" : "notebook", "status" : "A", "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "item" : "postcard", "status" : "A", "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

答案是沒有限制，但是會以最後一個欄位值為準，比如上面的就是文件僅僅不顯示 _id 欄位

 

> db.inventory.find( { status: "A" }, { _id : 0,_id : 1 } )
{ "_id" : ObjectId("60b7177a67b3da741258754b") }
{ "_id" : ObjectId("60b7177a67b3da741258754c") }
{ "_id" : ObjectId("60b7177a67b3da741258754f") }

像這個就是文件僅僅顯示 _id 欄位

 

返回巢狀文件的指定欄位

> db.inventory.find(
...    { status: "A" },
...    { item: 1, status: 1, "size.uom": 1 }
... )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "status" : "A", "size" : { "uom" : "cm" } }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "status" : "A", "size" : { "uom" : "in" } }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "status" : "A", "size" : { "uom" : "cm" } }
  • 查詢條件：status 等於 A
  • 返回欄位：_id、item、status、size 巢狀文件的 uom 欄位

 

關於指定巢狀文件的欄位，4.4 新增的新寫法

> db.inventory.find(    { status: "A" },    { item: 1, status: 1, size: {uom : 1 } } )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "status" : "A", "size" : { "uom" : "cm" } }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "status" : "A", "size" : { "uom" : "in" } }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "status" : "A", "size" : { "uom" : "cm" } }

其實就是將  "size.uom": 1 替換成 size : { uom : 1 } ，兩種寫法哪種順手用哪種

 

返回文件陣列中的文件的指定欄位

instock 文件陣列中的文件，只返回 qty 欄位

> db.inventory.find( { status: "A" }, { item: 1, status: 1, "instock.qty": 1 } )
{ "_id" : ObjectId("60b7177a67b3da741258754b"), "item" : "journal", "status" : "A", "instock" : [ { "qty" : 5 } ] }
{ "_id" : ObjectId("60b7177a67b3da741258754c"), "item" : "notebook", "status" : "A", "instock" : [ { "qty" : 5 } ] }
{ "_id" : ObjectId("60b7177a67b3da741258754f"), "item" : "postcard", "status" : "A", "instock" : [ { "qty" : 15 }, { "qty" : 35 } ] }

 

相關文章