MongoDB查询操作返回指定的字段

求知探索 1年前 ⋅ 835 阅读

插入测试数据

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

 


注意：本文归作者所有，未经作者允许，不得转载

#MongoDB
阅读全部

全部评论: 0 条

    我有话说: