logo
搜索
我的图书馆

发文章

发文工具

其他工具

留言交流
搜索
分享

《MongoDB高手课》学习记录（第十天）

 python_lover 2020-02-12

踩坑记录

可能是之前的理解有问题，昨天看了一下官方文档，通过brew是可以直接安安装MongoDB Community的最新版本的。当然企业版本是没啥办法，还得下载压缩包，然后自己配置。

安装过程

需要的环境

  1. 4.2 版本的MongoDB只支持 10.12 版本以上的MacOS。
  2. 需要安装 xcode
  3. 当然还需要安装 Homebrew
  4. 需要安装 gcc
  5. 文档地址

安装过程

# 首先注册 MongoDB Homebrew Tap
$> brew tap mongodb/brew

# 现在我能查到的版本如下
$> brew search mongodb-community

==> Formulae
mongodb/brew/mongodb-community                           mongodb/brew/mongodb-community@3.2                       mongodb/brew/mongodb-community@3.6
mongodb/brew/mongodb-community-shell                     mongodb/brew/mongodb-community@3.4                       mongodb/brew/mongodb-community@4.0

# 默认没有打Tag的版本是4.2.2
$> brew info mongodb-community

mongodb/brew/mongodb-community: stable 4.2.2
High-performance, schema-free, document-oriented database
https://www./
Not installed
From: https://github.com/mongodb/homebrew-brew/blob/master/Formula/mongodb-community.rb
==> Caveats
To have launchd start mongodb/brew/mongodb-community now and restart at login:
  brew services start mongodb/brew/mongodb-community
Or, if you don't want/need a background service you can just run:
  mongod --config /usr/local/etc/mongod.conf
  
# 开始安装
$> brew install mongodb-community

==> Caveats
To have launchd start mongodb/brew/mongodb-community now and restart at login:
  brew services start mongodb/brew/mongodb-community
Or, if you don't want/need a background service you can just run:
  mongod --config /usr/local/etc/mongod.conf
==> Summary
?  /usr/local/Cellar/mongodb-community/4.2.2: 21 files, 274.5MB, built in 27 minutes 20 seconds

# 启动服务
$> brew services start mongodb/brew/mongodb-community

==> Successfully started `mongodb-community` (label: homebrew.mxcl.mongodb-community)

安装位置信息

  • 程序目录：/usr/local/Cellar/mongodb-community
  • 配置文件：/usr/local/etc/mongod.conf
  • 系统日志：/usr/local/var/log/mongodb
  • 数据目录：/usr/local/var/mongodb

第十天

今日复习内容为CRUD操作，直接在Shell操作。都是官网的例子哈。
地址在这里：参考文档

准备练手数据

> db.inventory.insertMany([
...    { item: "journal", qty: 25, size: { h: 14, w: 21, uom: "cm" }, status: "A" },
...    { item: "notebook", qty: 50, size: { h: 8.5, w: 11, uom: "in" }, status: "A" },
...    { item: "paper", qty: 100, size: { h: 8.5, w: 11, uom: "in" }, status: "D" },
...    { item: "planner", qty: 75, size: { h: 22.85, w: 30, uom: "cm" }, status: "D" },
...    { item: "postcard", qty: 45, size: { h: 10, w: 15.25, uom: "cm" }, status: "A" }
... ]);
{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("5e06f1f5c01e4d7f78f96cc9"),
        ObjectId("5e06f1f5c01e4d7f78f96cca"),
        ObjectId("5e06f1f5c01e4d7f78f96ccb"),
        ObjectId("5e06f1f5c01e4d7f78f96ccc"),
        ObjectId("5e06f1f5c01e4d7f78f96ccd")
    ]
}

查询操作

查询所有记录

select * from inventory

> db.inventory.find( {} )
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }
> db.inventory.find()
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

我发现，传不传 {} 空对象，结果都一样哈。

指定查询条件

SELECT * FROM inventory WHERE status = "D"
对应的MQL如下

> db.inventory.find({"status": "D"})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }

SELECT * FROM inventory WHERE status in ("A", "D")
对应的MQL如下：

> db.inventory.find({status: {$in:["A","D"]}})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

SELECT * FROM inventory WHERE status = "A" AND qty < 30
对应的MQL如下

> db.inventory.find({status:"A", qty:{$lt:30}})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
>> db.inventory.find({$and:[{status:"A"},{qty:{$lt:30}}]})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }

SELECT * FROM inventory WHERE status = "A" OR qty < 80
对应的MQL如下

> db.inventory.find({$or:[{status: "A"},{qty:{$lt: 80}}]})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

SELECT * FROM inventory WHERE status = "A" AND ( qty < 30 OR item LIKE "p%")
对应的MQL如下(这里面用到了正则表达式)

> db.inventory.find({$and:[{status: "A"},{$or:[{qty:{$lt: 30}},{item:/^p/}]}]})

{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

> db.inventory.find({$and:[{status: "A"},{$or:[{qty:{$lt: 30}},{item:{$regex:/^p/}}]}]})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

查询内嵌对象

比如我要查询到这条记录

{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }

我要这么写，也就是写上完整的条件

> db.inventory.find( { size: { h: 14, w: 21, uom: "cm" } } )
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }

换个顺序也不行，必须按顺序写

> db.inventory.find( { size: { w:21, h: 14, uom: "cm" } } )

少写个字段不行

> db.inventory.find( { size: { h: 14, w: 21} } )

坑爹，那怎么搞
这样，有了，“.” 操作符

> db.inventory.find({"size.w":21})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
>

加个条件

> db.inventory.find({"size.h":{$lte:10}})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }

再写复杂点

> db.inventory.find({"size.h":{$lte:10},"size.w":11,status:"A"})
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }

数组查询

> db.inventory.insertMany([
...    { item: "journal", qty: 25, tags: ["blank", "red"], dim_cm: [ 14, 21 ] },
...    { item: "notebook", qty: 50, tags: ["red", "blank"], dim_cm: [ 14, 21 ] },
...    { item: "paper", qty: 100, tags: ["red", "blank", "plain"], dim_cm: [ 14, 21 ] },
...    { item: "planner", qty: 75, tags: ["blank", "red"], dim_cm: [ 22.85, 30 ] },
...    { item: "postcard", qty: 45, tags: ["blue"], dim_cm: [ 10, 15.25 ] }
... ]);
{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("5e070270c01e4d7f78f96cce"),
        ObjectId("5e070270c01e4d7f78f96ccf"),
        ObjectId("5e070270c01e4d7f78f96cd0"),
        ObjectId("5e070270c01e4d7f78f96cd1"),
        ObjectId("5e070270c01e4d7f78f96cd2")
    ]
}
> db.inventory.find()
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd2"), "item" : "postcard", "qty" : 45, "tags" : [ "blue" ], "dim_cm" : [ 10, 15.25 ] }

和文档中对象的查询一样的问题，要查一个数据，常规的写法，必须保证写全元素，包括顺序也得一样。

> db.inventory.find({tags:["blank","red"]})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }

不写全了，查不到

> db.inventory.find({tags:["blank"]})

如果只想查询包括 blank 的数据，得这么写

> db.inventory.find({tags:"blank"})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }

顺序不一样，查不全数据

> db.inventory.find({tags:["red", "blank"]})
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
>

那怎么办，我就想查询包括 blank 和 red 的都行的数据，与顺序无关的。这样，加个 $all

> db.inventory.find({tags:{$all:["red", "blank"]}})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }

查询的至少一个元素值，dim_cm>20的数据

> db.inventory.find({dim_cm:{$gt:20}})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }
> db.inventory.find({dim_cm:{$gt:21}})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }

但要查询至少数组的一个元素值是，dim_cm>15 and dim_cm<20，这样写是不行的

> db.inventory.find( { dim_cm: { $gt: 15, $lt: 20 } } )
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd2"), "item" : "postcard", "qty" : 45, "tags" : [ "blue" ], "dim_cm" : [ 10, 15.25 ] }

需要引入 $elemMath 运算符

> db.inventory.find( { dim_cm: {$elemMatch: { $gt: 15, $lt: 20 }} } )
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd2"), "item" : "postcard", "qty" : 45, "tags" : [ "blue" ], "dim_cm" : [ 10, 15.25 ] }

那我只想查询数组中的第一个元素 dim_cm>25 的数据呢

> db.inventory.find({"dim_cm.1": {$gt: 25}})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }

查询指定数组长度的数据，比如我想得到tags的元素数量为3的数据

> db.inventory.find({tags: {$size: 3}})
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }

数组中对象的查询

> db.inventory.insertMany( [
...    { item: "journal", instock: [ { warehouse: "A", qty: 5 }, { warehouse: "C", qty: 15 } ] },
...    { item: "notebook", instock: [ { warehouse: "C", qty: 5 } ] },
...    { item: "paper", instock: [ { warehouse: "A", qty: 60 }, { warehouse: "B", qty: 15 } ] },
...    { item: "planner", instock: [ { warehouse: "A", qty: 40 }, { warehouse: "B", qty: 5 } ] },
...    { item: "postcard", instock: [ { warehouse: "B", qty: 15 }, { warehouse: "C", qty: 35 } ] }
... ]);
{
    "acknowledged" : true,
    "insertedIds" : [
        ObjectId("5e070b34c01e4d7f78f96cd3"),
        ObjectId("5e070b34c01e4d7f78f96cd4"),
        ObjectId("5e070b34c01e4d7f78f96cd5"),
        ObjectId("5e070b34c01e4d7f78f96cd6"),
        ObjectId("5e070b34c01e4d7f78f96cd7")
    ]
}
> db.inventory.find()
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cc9"), "item" : "journal", "qty" : 25, "size" : { "h" : 14, "w" : 21, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96cca"), "item" : "notebook", "qty" : 50, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "A" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccb"), "item" : "paper", "qty" : 100, "size" : { "h" : 8.5, "w" : 11, "uom" : "in" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccc"), "item" : "planner", "qty" : 75, "size" : { "h" : 22.85, "w" : 30, "uom" : "cm" }, "status" : "D" }
{ "_id" : ObjectId("5e06f1f5c01e4d7f78f96ccd"), "item" : "postcard", "qty" : 45, "size" : { "h" : 10, "w" : 15.25, "uom" : "cm" }, "status" : "A" }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cce"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96ccf"), "item" : "notebook", "qty" : 50, "tags" : [ "red", "blank" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd0"), "item" : "paper", "qty" : 100, "tags" : [ "red", "blank", "plain" ], "dim_cm" : [ 14, 21 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd1"), "item" : "planner", "qty" : 75, "tags" : [ "blank", "red" ], "dim_cm" : [ 22.85, 30 ] }
{ "_id" : ObjectId("5e070270c01e4d7f78f96cd2"), "item" : "postcard", "qty" : 45, "tags" : [ "blue" ], "dim_cm" : [ 10, 15.25 ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd4"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd5"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd6"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd7"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

首先，常规的写法，元素的数量，顺序都一致，否则查询不到数据

> db.inventory.find({instock: {warehouse: "A",qty: 5}})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
> db.inventory.find({instock: {qyt:5, warehouse: "A"}})
>

我要查询 instock 数组中，包含 qty，并且至少要有一个值是 qty <= 20的记录

> db.inventory.find({"instock.qty": {$lte: 20}})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd4"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd5"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd6"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd7"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

当然也可以指定数组的序号，比如上面的查询，我只想查询到序号为0的

> db.inventory.find({"instock.0.qty": {$lte: 20}})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd4"), "item" : "notebook", "instock" : [ { "warehouse" : "C", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd7"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }
>

目前说的都是一个元素的，如果是多个呢？还得用到$elemMatch

> db.inventory.find({"instock": {$elemMatch: {qty: {$gt: 10, $lte: 20}}}})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd5"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd7"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

比较一下下面这个，只满足一个条件即可，上面的则必须都满足才行

> db.inventory.find( { "instock.qty": { $gt: 10,  $lte: 20 } } )
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd5"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd6"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd7"), "item" : "postcard", "instock" : [ { "warehouse" : "B", "qty" : 15 }, { "warehouse" : "C", "qty" : 35 } ] }

这样也可以

> db.inventory.find({"instock": {$elemMatch: {qty: 5,warehouse: "A"}}})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }

但这样不行，也就是混合查询中不能使用 $elemMatch

> db.inventory.find({"instock": {$elemMatch: {qty: {$lte: 20,$gt: 10},warehouse: "A"}}})

那怎么办，这样处理，拆开

> db.inventory.find({"instock.qty": {$lte: 20,$gt: 10},"instock.warehouse": "A"})
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd3"), "item" : "journal", "instock" : [ { "warehouse" : "A", "qty" : 5 }, { "warehouse" : "C", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd5"), "item" : "paper", "instock" : [ { "warehouse" : "A", "qty" : 60 }, { "warehouse" : "B", "qty" : 15 } ] }
{ "_id" : ObjectId("5e070b34c01e4d7f78f96cd6"), "item" : "planner", "instock" : [ { "warehouse" : "A", "qty" : 40 }, { "warehouse" : "B", "qty" : 5 } ] }

最后

今天先这些了，数组与对象混合这个，只能多练习，要不然……

    本站是提供个人知识管理的网络存储空间，所有内容均由用户发布，不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息，谨防诈骗。如发现有害或侵权内容，请点击一键举报。
    转藏 分享 献花（0）

    来自： python_lover > 《待分类》

    举报/认领

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多