MongoDB（13）- 查询操作返回指定的字段
- 2021 年 6 月 2 日
- 笔记
- mongodb, 测试高级进阶技能系列 - 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 } ] }
