MongoDB University / M001
※このmemoは、Zennを利用する前のメモになります。MQLについてが中心です!
20210203:
code:bash
% mongoimport --host localhost --port 26000 --db m103 \
--username m103-admin --password m103-pass \
--drop --type json \
--authenticationDatabase admin --collection products --file /dataset/products.json
Enter password:
2021-02-02T15:47:44.854+0000 connected to: mongodb://localhost:26000/
2021-02-02T15:47:46.899+0000 9966 document(s) imported successfully. 0 document(s) failed to import.
bash-4.4#
% mongo --host localhost --port 26000 --username m103-admin --password m103-pass
MongoDB shell version v4.0.5
Enter password:
connecting to: mongodb://localhost:26000/?gssapiServiceName=mongodb
2021-02-02T15:32:12.041+0000 I CONTROL main mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"minCompatibleVersion" : 5,
"currentVersion" : 6,
"clusterId" : ObjectId("6019707bd58ef74aa7bcc5cd")
}
shards:
{ "_id" : "shard1", "host" : "shard1/localhost:27001,localhost:27002,localhost:27003", "state" : 1 }
{ "_id" : "shard2", "host" : "shard2/localhost:27007,localhost:27008,localhost:27009", "state" : 1 }
active mongoses:
"4.0.5" : 1
autosplit:
Currently enabled: yes
balancer:
Currently enabled: yes
Currently running: no
Failed balancer rounds in last 5 attempts: 0
Migration Results for the last 24 hours:
No recent migrations
databases:
{ "_id" : "config", "primary" : "config", "partitioned" : true }
config.system.sessions
shard key: { "_id" : 1 }
unique: false
balancing: true
chunks:
shard1 1
{ "_id" : { "$minKey" : 1 } } -->> { "_id" : { "$maxKey" : 1 } } on : shard1 Timestamp(1, 0)
{ "_id" : "m103", "primary" : "shard2", "partitioned" : false, "version" : { "uuid" : UUID("9aa1380b-9e46-448f-bcdb-059c378bf2a5"), "lastMod" : 1 } }
mongos>
use m103
sh.enableSharding("m103")
db.products.findOne()
db.products.createIndex( { "sku" : 1 } )
sh.shardCollection("m103.products", {"sku" : 1 } )
sh.status()
mongos> show databases
admin 0.000GB
config 0.000GB
m103 0.000GB
mongos> use m103
switched to db m103
mongos> show collections
products
mongos> db.products.count()
9966
mongos> db.products.findOne()
{
"_id" : ObjectId("573f7197f29313caab89b21b"),
"sku" : 20000008,
"name" : "Come Into The World - CD",
"type" : "Music",
"regularPrice" : 14.99,
"salePrice" : 14.99,
"shippingWeight" : "0.25"
}
mongos> sh.enableSharding("m103")
{
"ok" : 1,
"operationTime" : Timestamp(1612282607, 10),
"$clusterTime" : {
"clusterTime" : Timestamp(1612282607, 10),
"signature" : {
"hash" : BinData(0,"Wu/7F8whQEZAQC2WEk1WCq+8Lqw="),
"keyId" : NumberLong("6924700149851619357")
}
}
}
mongos> db.products.createIndex( { "sku" : 1 } )
{
"raw" : {
"shard1/localhost:27001,localhost:27002,localhost:27003" : {
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
},
"ok" : 1,
"operationTime" : Timestamp(1612282631, 2),
"$clusterTime" : {
"clusterTime" : Timestamp(1612282631, 2),
"signature" : {
"hash" : BinData(0,"K0oRKhE0uBGAg8xzGUA6tewsKBc="),
"keyId" : NumberLong("6924700149851619357")
}
}
}
mongos> sh.status()
--- Sharding Status ---
sharding version: {
"_id" : 1,
"minCompatibleVersion" : 5,
"currentVersion" : 6,
"clusterId" : ObjectId("60197a19d32f62a67ddcb490")
}
shards:
{ "_id" : "shard1", "host" : "shard1/localhost:27001,localhost:27002,localhost:27003", "state" : 1 }
{ "_id" : "shard2", "host" : "shard2/localhost:27007,localhost:27008,localhost:27009", "state" : 1 }
active mongoses:
"4.0.5" : 1
autosplit:
Currently enabled: yes
balancer:
Currently enabled: yes
Currently running: no
Failed balancer rounds in last 5 attempts: 0
Migration Results for the last 24 hours:
No recent migrations
databases:
{ "_id" : "config", "primary" : "config", "partitioned" : true }
{ "_id" : "m103", "primary" : "shard1", "partitioned" : true, "version" : { "uuid" : UUID("ebc4c237-5c7c-485d-9647-ad0cd79b90dc"), "lastMod" : 1 } }
m103.products
shard key: { "sku" : 1 }
unique: false
balancing: true
chunks:
shard1 1
{ "sku" : { "$minKey" : 1 } } -->> { "sku" : { "$maxKey" : 1 } } on : shard1 Timestamp(1, 0)
20201121: Chapter 5 / Indexing and Aggregation Pipeline
まずはアグリゲーション
これは MQLのfindと同じように基本は検索できるけれど、その処理を「パイプライン」としてつなげて実行できるもの!
検索 -> フィルタ(プロジェクション)-> 加工といった感じで、配列に処理を指定して実行
https://gyazo.com/2a3e1c4fc26a83261fe902ed55f8d76f
やっと4章までおわり!あと2週間以内に終わらせられるかな??
https://gyazo.com/05a3267e625716d54fbd82a1f13e52f7
Chapter 4: / Quiz: Querying Arrays and Sub-documents
Which of the following queries will return the names and addresses of all listings from the sample_airbnb.listingsAndReviews collection where the first amenity in the list is "Internet"?
最初のアメニティにインターネットが上がっているものを抽出するクエリは?
code:bash
query = { "amenities.0": "Internet" }
// これが正解!
db.listingsAndReviews.find({ "amenities.0": "Internet" },
{ "name": 1, "address": 1 }).pretty()
Chapter 4: / Lab 2: Querying Arrays and Sub-Documents
How many inspections from the sample_training.inspections collection were conducted in the city of NEW YORK?
NYに運営されているのはどれくらい? -> NYの管轄下での調査の件数はどれくらい?
code:bash
query = { "address.city" : "NEW YORK" }
db.inspections.find(query).count()
18279
Chapter 4: / Lab 1: Querying Arrays and Sub-Documents
How many trips in the sample_training.trips collection started at stations that are to the west of the -74 latitude coordinate
coordinate = 座標
latitudeは start station location.coodinates.1
code:bash
/*
// こんな感じにネストしている
"start station location": {
"type": "Point",
}
座標の並びは緯度 (latitude)経度 (ではなく、経度緯度みたい = longitude / latitude )
アメリカ合衆国/@40.7792913,-73.9833372 (GoogleMapではこの順:緯度経度 = latitude / longitude)
*/
use sample_training
query = { "start station location.coodinates.1": -74 }
db.trips.find(query).count()
// 正しい答え
// 西側になるのは -74より小さい(-75, -76 ... のところ)
db.trips.find({ "start station location.coordinates": { "$lt": -74 }}).count()
ちょっと意味がわからなかったので残念ながら❌
The "start station location" has a sub-document that contains the coordinates array.
"start station location" はサブドキュメントがあります
To get to this coordinates array we must use use dot-notation. We can issue a range query to find all documents in this latitude.
座標の配列はドット指定で条件を特定できます
また、該当のデータはレンジ指定(greater than, less than... )で取得できます
The caveat is to remember that all trips take place in NYC so the longitude value in the coordinates array will always be positive, and we don't have to worry about it when issuing a range query like this.
caveat = 注意書き、警告
登録されている全ての旅行はニューヨークを起点にしているので、座標の経度は常に「正」になります
Chapter 4: / Array Operators and Sub-Documents
code:bash
// 抽出条件をネストして設定できます!すごい!!!
// start station locationフィールドでネストしたドキュメントのtypeがPointのものを1つピックアップ
db.trips.findOne({ "start station location.type": "Point" })
{ _id: ObjectID("572bb8222b288919b68abf5a"),
tripduration: 379,
'start station id': 476,
'start station name': 'E 31 St & 3 Ave',
'end station id': 498,
'end station name': 'Broadway & W 32 St',
bikeid: 17827,
usertype: 'Subscriber',
'birth year': 1969,
gender: 1,
'start time': Fri Jan 01 2016 09:00:45 GMT+0900 (Japan Standard Time),
'stop time': Fri Jan 01 2016 09:07:04 GMT+0900 (Japan Standard Time) }
// companiesドキュメント
// Zuckerbergを含む会社(1つだけ!)
db.companies.find({ "relationships.0.person.last_name": "Zuckerberg" },
{ "name": 1, "relationships" }).pretty()
// firstNameがMarkでCEOの肩書き(部分一致)の件数はたくさん!
db.companies.find({ "relationships.0.person.first_name": "Mark",
"relationships.0.title": { "$regex": "CEO" } },
{ "name": 1 }).count()
52
// firstNameがMarkで過去の肩書き(部分一致)の件数は、もっとたくさん!
db.companies.find({ "relationships":
{ "$elemMatch": { "is_past": true,
"person.first_name": "Mark" } } },
{ "name": 1 }).count()
256
Chapter 4: / Quiz: Array Operators and Projection
Which of the following queries will return only the names of companies from the sample_training.companies collection that had exactly 8 funding rounds?
配列型でのfunding_roundsフィールドがある
code:bash
use sample_training
projection = { name: 1 }
// 8 funding rounds
query = { funding_rounds: { $size: 8 } }
// count: 21
db.companies.find(query)
db.companies.find(query, projection)
{ _id: ObjectID("52cdef7c4bab8bd675297d94"), name: 'Twitter' }
{ _id: ObjectID("52cdef7c4bab8bd675297e0c"), name: 'LinkedIn' }
{ _id: ObjectID("52cdef7c4bab8bd675297e6d"), name: 'PayScale' }
{ _id: ObjectID("52cdef7c4bab8bd675297fe7"), name: 'Xobni' }
{ _id: ObjectID("52cdef7c4bab8bd6752981e1"), name: 'Zynga' }
{ _id: ObjectID("52cdef7c4bab8bd675298521"), name: 'ShareThis' }
{ _id: ObjectID("52cdef7d4bab8bd675298959"),
name: 'TicketLeap' }
{ _id: ObjectID("52cdef7d4bab8bd675298b52"), name: 'Moblyng' }
{ _id: ObjectID("52cdef7d4bab8bd675299e82"),
name: 'PlumChoice' }
{ _id: ObjectID("52cdef7d4bab8bd675299f34"), name: 'SolFocus' }
{ _id: ObjectID("52cdef7e4bab8bd67529a4b2"), name: 'HyperWeek' }
{ _id: ObjectID("52cdef7e4bab8bd67529a951"),
name: 'Virident Systems' }
{ _id: ObjectID("52cdef7e4bab8bd67529abb8"),
name: 'Extreme Enterprises' }
{ _id: ObjectID("52cdef7e4bab8bd67529af2a"), name: 'CipherMax' }
{ _id: ObjectID("52cdef7e4bab8bd67529b71e"), name: 'Stemgent' }
{ _id: ObjectID("52cdef7e4bab8bd67529b915"), name: 'Sonos' }
{ _id: ObjectID("52cdef7e4bab8bd67529b9e3"), name: 'BridgeLux' }
{ _id: ObjectID("52cdef7f4bab8bd67529c001"),
name: 'Silicor Materials' }
{ _id: ObjectID("52cdef7f4bab8bd67529c017"),
name: '1366 Technologies' }
{ _id: ObjectID("52cdef7f4bab8bd67529c169"),
name: 'Biolex Therapeutics' }
Type "it" for more
db.companies.find(query, projection).count()
21
// answer
projection = { name: 1, _id: 0 }
query = { funding_rounds: { $size: 8 } }
db.companies.find(query, projection)
20201121: Chapter 4: / Lab: Array Operators and Projection
How many companies in the sample_training.companies collection have offices in the city of Seattle?
code:bash
use sample_training
// 117
db.companies.find({ offices: { "$elemMatch": { city: "Seattle" } } }).count()
// _id, name, offices に絞る
db.companies.find({ offices: { "$elemMatch": { city: "Seattle" } } }, { offices: 1, name: 1 })
{ _id: ObjectID("52cdef7c4bab8bd675297d8a"),
name: 'Wetpaint',
offices:
[ { description: '',
address1: '710 - 2nd Avenue',
address2: 'Suite 1100',
zip_code: '98104',
city: 'Seattle',
state_code: 'WA',
country_code: 'USA',
latitude: 47.603122,
longitude: -122.333253 },
{ description: '',
address1: '270 Lafayette Street',
address2: 'Suite 505',
zip_code: '10012',
city: 'New York', // この場合はSeattleでないものも含む
state_code: 'NY',
country_code: 'USA',
latitude: 40.7237306,
longitude: -73.9964312 } ] }
{ _id: ObjectID("52cdef7c4bab8bd675297dc9"),
name: 'Jobster',
offices:
[ { description: null,
address1: '3131 Elliott Avenue Suite 600',
address2: null,
zip_code: '98121',
city: 'Seattle',
state_code: 'WA',
country_code: 'USA',
latitude: 47.617682,
longitude: -122.357242 } ] }
.....
// Projectionにも$elemMatchを使うと、officesフィールドの配列のうち、Seattleを含むものだけを表示
db.companies.find({ offices: { "$elemMatch": { city: "Seattle" } } }, { name: 1, offices: { "$elemMatch": { city: "Seattle" } } })
{ _id: ObjectID("52cdef7c4bab8bd675297d8a"),
name: 'Wetpaint',
offices:
[ { description: '',
address1: '710 - 2nd Avenue',
address2: 'Suite 1100',
zip_code: '98104',
city: 'Seattle',
state_code: 'WA',
country_code: 'USA',
latitude: 47.603122,
longitude: -122.333253 } ] }
{ _id: ObjectID("52cdef7c4bab8bd675297dc9"),
name: 'Jobster',
offices:
[ { description: null,
address1: '3131 Elliott Avenue Suite 600',
address2: null,
zip_code: '98121',
city: 'Seattle',
state_code: 'WA',
country_code: 'USA',
latitude: 47.617682,
longitude: -122.357242 } ] }
......
20201120: chapter: 4 / Array Operators and Projection
フィールドを指定して表示します (Projection)
findの定義
db.collection.find(query, projection)
シンプルなprojectionは、フィールド名と0 or 1で指定
$elemMatch を使ってプロジェクションでの絞り込みが可能(マッチするデータのみ)
Using the array projection operators $elemMatch, $slice, specifies the array element(s) to include, thereby excluding those elements that do not meet the expressions. (Not available for views.)
$elemMatch, $sliceを使うと、その値を含む配列だけを取り出します
つまり、それ以外のものは表示しません
実は$elemMatch, $sliceは第一引数(query) にも使える
ただし、プロジェクションで使うと、該当する値だけを表示できる
code:bash
// use sample_training
// Find all documents where the student in class 431 received a grade
// higher than 85 for any type of assignment:
// 最初のクエリでクラスIDが431に絞り込み
// 2番目の引数はプロジェクション。
// $elemMatchを使う例なので、ここでは、socoresフィールド(配列になっている)を指定
// その中でもscoreが85より大きいものだけを取り出す
db.grades.find({ "class_id": 431 },
{ "scores": { "$elemMatch": { "score": { "$gt": 85 } } }
}).pretty()
....
db.grades.find({ "class_id": 431 },
{ "scores": { "$elemMatch": { "score": { "$gt": 85 } } }
}).pretty()
{ _id: ObjectID("56d5f7eb604eb380b0d8d8fb") } // scores にマッチしたものがない場合は_idのみ
{ _id: ObjectID("56d5f7eb604eb380b0d8dbf2") }
{ _id: ObjectID("56d5f7eb604eb380b0d8dca5"),
....
....
db.grades.find({ "class_id": 431, scores: { $size: 1 } },
{ "scores": { "$elemMatch": { "score": { "$gt": 85 } } }
}).pretty()
{ _id: ObjectID("56d5f7eb604eb380b0d8d8fb") }
{ _id: ObjectID("56d5f7eb604eb380b0d8dbf2") }
{ _id: ObjectID("56d5f7eb604eb380b0d8dca5"),
// アメニティで提供される数が20個あり、配列でわたされたアメニティをカバーしていること(13個必須で、20個)
// 取り出す列は _id, price, address
db.listingsAndReviews.find({ "amenities":
{ "$size": 20, "$all": [ "Internet", "Wifi", "Kitchen", "Heating",
"Family/kid friendly", "Washer", "Dryer",
"Essentials", "Shampoo", "Hangers",
"Hair dryer", "Iron",
"Laptop friendly workspace" ] } },
{"price": 1, "address": 1}).pretty()
// findの2つ目の引数でprojectionを設定します
Mongodb compassを使う場合は、列指定はProjectのところで。
https://gyazo.com/0c97de0710ff4b9f33aa6b9fe4a7ab85
chapter: 4 / Quiz: Array Operators
Which of the following queries will return all listings that have "Free parking on premises", "Air conditioning", and "Wifi" as part of their amenities, and have at least 2 bedrooms in the sample_airbnb.listingsAndReviews collection?
premise = 敷地内(無料駐車場)
code:bash
db.listingsAndReviews.find({
bedrooms: { $gte: 2 }
}).count()
430
// answer
db.listingsAndReviews.find({
"bedrooms": { "$gte": 2 }
}).pretty()
chapter: 4 / Lab 2: Array Operators
Using the sample_airbnb.listingsAndReviews collection find out how many documents have the "property_type" "House", and include "Changing table" as one of the "amenities"?
アメニティに"Changing table"を含む
property_typeがHouse
code:bash
db.listingsAndReviews.find({
property_type: "House",
amenities: "Changing table"
}).count()
11
chapter: 4 / Lab 1: Array Operators
What is the name of the listing in the sample_airbnb.listingsAndReviews dataset that accommodates more than 6 people and has exactly 50 reviews?
accommodate = 収容人数、宿泊可能な人数
6名以上で50のレビューがあること
Copy/Paste the value of the "name" field into the response field without quotation marks.
code:bash
db.listingsAndReviews.find({
accommodates: { $gt: 6 },
reviews: { $size: 50 }
}).count()
// または
db.listingsAndReviews.find({ $and:
[{ accommodates: { $gt: 6 } },
{ reviews: { $size: 50 } }
]}).count()
1
db.listingsAndReviews.find({
accommodates: { $gt: 6 },
reviews: { $size: 50 }
}, { "name": 1 })
20201119: chapter: 4/ Array Operators
array operatorについて学習します
$push をすでに学んでいますね!
要素を追加したり、フィールドを配列に置き換えたりできます
基本的にはMQLはフィールドの検索に配列を使うと、配列の中身に完全一致(順番考慮)しないと値を返しません
順番をケアしない場合は、$all を使うこと!
こんドキュメントはフィールドが多すぎて比較しにくいので、表示を減らす(migitateする)には、この次のレッスンで実施します
code:bash
// { amenities: "Shampoo" } (amenitiesは配列)
use sample_airbnb
'switched to db sample_airbnb'
show collections
listingsAndReviews
db.listingsAndReviews.find().count()
5555
// 配列の中身にヒット
db.listingsAndReviews.find({ amenities: "Shampoo" }).count()
3709
// 1つピックアップ
db.listingsAndReviews.findOne({ amenities: "Shampoo" }).amenities
[ 'TV',
'Cable TV',
'Wifi',
'Air conditioning',
'Pool',
'Kitchen',
'Free parking on premises',
'Elevator',
'Hot tub',
'Washer',
'Dryer',
'Essentials',
'Shampoo',
'Hangers',
'Hair dryer',
'Iron',
'Laptop friendly workspace',
'Self check-in',
'Lockbox',
'Hot water',
'Bed linens',
'Extra pillows and blankets',
'Ethernet connection',
'Microwave',
'Coffee maker',
'Refrigerator',
'Dishes and silverware',
'Cooking basics',
'Stove',
'BBQ grill',
'Garden or backyard',
'Well-lit path to entrance',
'Disabled parking spot',
'Step-free access',
'Wide clearance to bed',
'Step-free access' ]
// 配列は全て順番通りにマッチする形でないと検索にひっかからない
ar = "TV", "Cable TV", "Wifi", "Air conditioning", "Pool", "Kitchen", "Free parking on premises", "Elevator", "Hot tub", "Washer", "Dryer", "Essentials", "Shampoo", "Hangers", "Hair dryer", "Iron", "Laptop friendly workspace", "Self check-in", "Lockbox", "Hot water", "Bed linens", "Extra pillows and blankets", "Ethernet connection", "Microwave", "Coffee maker", "Refrigerator", "Dishes and silverware", "Cooking basics", "Stove", "BBQ grill", "Garden or backyard", "Well-lit path to entrance", "Disabled parking spot", "Step-free access", "Wide clearance to bed", "Step-free access" db.listingsAndReviews.find({ amenities: ar }).count()
1
// 条件を確認
ar.length
36
// 一個要素を削除
ar.pop()
'Step-free access'
ar.length
35
// もう一回検索すると、マッチしない。(そのままだと完全一致)
// stringで比較すると、要素のどれかにヒットすればOK
db.listingsAndReviews.find({ amenities: ar }).count()
0
// $all を使うと部分一致で順番問わずに検索できます!
db.listingsAndReviews.find({ amenities: { $all: ar } }).count()
1
// 条件を10個に変更
ar = ar.slice(0,10)
ar.length
10
// 増えました
db.listingsAndReviews.find({ amenities: { $all: ar } }).count()
61
// そのうち、アメニティが20個あるものを抽出
db.listingsAndReviews.find({ amenities: { $size: 20, $all: ar } }).count()
1
// 以下の場合、件数は8件だけど、とても長い...
db.listingsAndReviews.find({ "amenities": {
"$size": 20,
"$all": [ "Internet", "Wifi", "Kitchen",
"Heating", "Family/kid friendly",
"Washer", "Dryer", "Essentials",
"Shampoo", "Hangers",
"Hair dryer", "Iron",
"Laptop friendly workspace" ]
}
}).pretty()
db.listingsAndReviews.find({ "amenities": {
"$size": 20,
"$all": [ "Internet", "Wifi", "Kitchen",
"Heating", "Family/kid friendly",
"Washer", "Dryer", "Essentials",
"Shampoo", "Hangers",
"Hair dryer", "Iron",
"Laptop friendly workspace" ]
}
}, {'_id' :1 }).pretty()
// _id だけ帰ってきた!
{ _id: '10992286' }
{ _id: '11914814' }
{ _id: '1459741' }
{ _id: '15747156' }
{ _id: '15864211' }
{ _id: '31037891' }
{ _id: '6530907' }
{ _id: '6807419' }
20201117: chapter: 4 / Expressive Query Operator
$expr = Expressive Query Operator
シンプルなオペレータよりも高度なことができます
より高度で複雑なオペレータ
notch (切り替え、段階)
versatility : バーサティリティ。汎用性、多用性
aggregation: 集団、集合、集約すること
たとえば、ドキュメント内のフィールド同士を比較するにはどうすればいい?
$exprを使えます
code:bash
// この例の場合
// $expr を使うと、フィールドの値同士を変数に入れて比較できる
// $__フィールド名__ で、 $をつけることでフィールドの値を代入できる
// この例では、出発地点のIDと到着地点のIDが同じ場所を取り出す、ということになる
{ "$expr":
}
316
// 出発と到着が同じ、かつ、tripdurationが1200より大きい(more than 2 minutes)
db.trips.find({ "$expr":
// 173
db.trips.find({ "$expr":
] }
}).count()
Chapter 4: Advanced CRUD Operations / Quiz 1: $expr
What are some of the uses for the $ sign in MQL?
$の意味、利用方法は?
変数、$expr 利用時はフィールド名
$ denotes an operator. (denote = 示す)
This is correct. オペレータの意味での接頭辞
All MQL operators have the $ prefix.
signifies that you are looking at the value of that field rather than the field name.
This is correct. フィールド名でなく接頭辞をつけることでフィールドの「値」を表現する
When $ is used to prefix a field name, it represents the value stored in that field. This is very helpful for expressions that compare fields within the same document.
同じドキュメントの値同志を比較する際にとても便利です
Chapter 4: Advanced CRUD Operations / Lab: $expr
How many companies in the sample_training.companies collection have the same permalink as their twitter_username?
code:bash
condition = {
}
db.companies.find(condition).count()
// 1299
20201116: chapter: 4
code:bash
// How many documents in the sample_training.zips collection have fewer than 1000 people listed in the pop field?
// Mine
db.zips.find({ "pop": { $lt: 1000 }}).count()
8065
// Lesson's answer
db.zips.find({ "pop": { "$lt": 1000 }}).count()
Lab 2: Comparison Operators
code:bash
// What is the difference between the number of people born in 1998 and the number of people born after 1998 in the sample_training.trips collection?
// NOTE: field name is "birth year"
use sample_training
a = db.trips.find({ "birth year": { $eq: 1998 }}).count() // 1
b = db.trips.find({ "birth year": { $gt: 1998 }}).count() // 18
b - a
6
// Lesson's answer
db.trips.find({ "birth year": { "$gt": 1998 }}).count()
db.trips.find({ "birth year": 1998 }).count()
Lab 3: Comparison Operators
code:bash
// Using the sample_training.routes collection find out which of the following statements will return all routes that have at least one stop in them?
少なくとも一回停止するルートをさがしてね!
// MINE:
db.routes.find({ "stops": { $gte: 1 } })
// Lesson's answer (choose the same result)
db.routes.find({ "stops": { "$ne": 0 }}).pretty()
db.routes.find({ "stops": { "$gt": 0 }}).pretty()
Query Operators - Logic
dst_airportがKZNかsrc_airportがKZN (出発かとうちゃく、どっちかがカザフスタン)
かつ
airplaneがCR2かA81
code:bash
// 18
db.routes.find({ "$and":
[
]
}).pretty()
// 56
317
Quiz 1: Logic Operators
How many businesses in the sample_training.inspections dataset have the inspection result "Out of Business" and belong to the "Home Improvement Contractor - 100" sector?
"Out of Business" (関係無し)かつ、"Home Improvement Contractor - 100" なもの
code:bash
// 7038
db.inspections.find({ "result": "Out of Business" },
{ "sector": "Home Improvement Contractor - 100" }).count()
// use $and = 4
db.inspections.find({ $and: [{ "result": "Out of Business" },
{ "sector": "Home Improvement Contractor - 100" }] }).count()
Quiz 2: Logic Operators
最も簡潔なクエリはどれ?(succinct = 簡潔、明瞭な)
Which is the most succinct query to return all documents from the sample_training.inspections collection where the inspection date is either "Feb 20 2015", or "Feb 21 2015" and the company is not part of the "Cigarette Retail Dealer - 127" sector?
日付が"Feb 20 2015"あるいは"Feb 21 2015" (or)
かつ (and)
"Cigarette Retail Dealer - 127"でないもの (not)
code:bash
{ $and: [
{ $or: "date": "Feb 20 2015" }, { "date": "Feb 21 2015" } },
{ $not: { "sector": "Cigarette Retail Dealer - 127" } }
] }
Lab 1: Logic Operators
How many zips in the sample_training.zips dataset are neither over-populated nor under-populated? (人口が非常に増加している、もしくは増加傾向のものは除外)
In this case, we consider population of more than 1,000,000 to be over- populated and less than 5,000 to be under-populated.
1,000,000より大きいものはover- populatedとする
5,000以下のものはunder-populatedとする
Copy/paste the exact numeric value of the result that you get into the response field.
code:bash
// 11193
{ $nor: [
{ "pop": { $gt: 1000000 } },
{ "pop": { $lte: 5000 } },
]
}.count()
Lab 2: Logic Operators
How many companies in the sample_training.companies dataset were either founded in 2004, or in the month of October and either have the social category_code or web category_code?
設立年が "2004" あるいは 設立月が "10月" (or)
かつ (and)
category_codeが "web" か "social" なもの (or)
code:bash
{ $and: [
]
}
// 149
db.companies.find(
{ $and: [
]
}
).count()