MongoDB（13）- 查询操作返回指定的字段

插入测试数据

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 } ] }