연습문제

CRUD

insert() exercises

Exercise

Insert one document to pypeople collection
- _id:0, name: Jisu, age: 24
결과

{'_id': 0, 'age': 24, 'name': 'Jisu’}

Insert multiple document
- _id: 1, name: Deayoung, age: 51
- _id: 2, name: Youngjin, age: 24
- Print all documents
결과

 [{'_id': 0, 'age': 24, 'name': 'Jisu’},
  {'_id': 1, 'age': 51, 'name': 'Deayoung’},
  {'_id': 2, 'age': 24, 'name': 'Youngjin'}]

find() Exercises

Exercise 1

Preliminary

Import people.json into lab4.people (i.e. database name is lab4 and collection name is people)

$ mongoimport -d lab4 -c people people.json

Questions

Find the documents whose age under 22.
Find the documents whose age is between 22 and 26 (inclusive).
Find the document whose name is “Lee” suppressing the profile.
Find the documents whose name is not Lee.
Find the documents whose name is “Lee” or “Park”.
Find the documents whose name is not “Kim” or “Park” or “Lee”.

Exercise 2

Preliminary

Import inventory.json into lab4.inventory

Questions

Find documents where the tags field contains below elements.
- appliance, school, and book.
- Hint: recall $all operator
Find all documents but select at most three items in the tags field.
- Hint: recall $slice operator
Find all documents but select items from the second to the fourth in the tags field.
- Hint: recall $slice operator with [skip, limit]
Find documents that have brown color in the qty field.
- Hint: recall $elemMatch operator

Exercise 3

Preliminary

Import store.json to the store collection

Questions

Find documents where buyer's name is "J.S. Lee"
- Hint: Querying embedded document.
Print purchased items where a name in a document is "iPhone Xs"
- Hint: Querying embedded document, Projection
Print the buyer’s purchase method where items contain the document that price is more than 2000$
- Hint: < array.field >, Projection
Find documents that don’t have carrier as key in attribute where the name is "Apple Watch Series 4"
- Hint: $elemMatch, $exists

Exercise 4

Preliminary

Import store.json to the store collection
- Same with Exercise 3!

Questions

Find documents where buyer's name is "J.S. Lee"
- Hint: Querying embedded document.
Print purchased items where a name in a document is "iPhone Xs".
- Hint: Querying embedded document, Projection
Print the buyer’s purchase method where items contain the document that price is more than 2000$.
- Hint: <array.field>, Projection
Find documents that don’t have carrier as key in attribute where the name is "Apple Watch Series 4"
- Hint: $elemMatch, $exists

Exercise 5

Preliminary

Import store.json to the store collection
- Same with Exercise 3!

Questions

Get the first three documents and print buyers field only.
Skip a document and then print two documents
Count documents in the store collection.

Exercise 6

Preliminary

Using grade.json (import it using mongoimport)

Questions

The grade’s document can have among three types:
- quiz, exam or homework
Student id (sid) is from 0 to 99
A document is lost, which contains a quiz score 1. print lost document’s sid. 2. Insert the quiz score (80) to the corresponding sid 3. print the three students that have the highest quiz score.
A simple example of the dataset looks like ...

{"sid": 0, "type": "homework", "score": 69}
{"sid": 0, "type": "quiz" , "score": 29}
{"sid": 0, "type": "exam", "score": 86}
{"sid": 1, "type": "homework", "score": 2}
{"sid": 1, "type": "quiz" , "score": 6}
{"sid": 1, "type": "exam", "score": 67}
{"sid": 2, "type": "homework", "score": 13}
{"sid": 2, "type": "quiz" , "score": 91}
{"sid": 2, "type": "exam", "score": 76}
...

Exercise 7

Preliminary

Using grade.json (import it using mongoimport)
- Same with exercise 6

Questions

Write the score searching script
- Search documents by filtering type and score fields

Results

search > homework 13
sid homework
2 13
74 13

search > quiz 41-49
sid quiz
10 46
17 48
19 44
...
96 49
98 41

update() Exercise

Set a 'location' field to 'Seoul' to a document.

The document has a 'name‘, 'Deayoung‘.

[{'_id': 0, 'age': 24, 'name': 'Jisu'},
 {'_id': 1, 'age': 51, 'location': 'Seoul', 'name': 'De ayoung'},
 {'_id': 2, 'age': 24, 'name': 'Youngjin'}]

Set a 'location' field to 'Suwon' to documents.
- The documents have a 'age‘, 24.

[{'_id': 0, 'age': 24, 'location': 'Suwon', 'name': 'Jisu'},
 {'_id': 1, 'age': 51, 'location': 'Seoul', 'name': 'De ayoung'},
 {'_id': 2, 'age': 24, 'location': 'Suwon', 'name': 'Y oungjin'}]

delete() exercise

Delete documents

- Remove documents that have a 'location‘, 'Suwon‘.
Print all documents

{'_id': 1, 'age': 51, 'location': 'Seoul', 'name': 'Deayoung'}

explain() Exercise

Print a query plan using executionStats corresponding to the query that finds writer, Kim.
Using blog.json

 "executionStats" : {
…
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 4,
                "nReturned": 1,
                "executionStages": {
                               "stage": "COLLSCAN",
…

CRUD(Advanced)

Use a ex1 database and imdb collection.
We are going to make a movie database like IMDb.
Each movie has following information.
- title: String
- director: String
- genre: List(String)
- score: Double
Insert a movie
- Write a script that inserts movie information to a movie collection.
- Type the movie information as follows:
- Movie Title: Avengers: Infinity War
  Director: Anthony Russo and Joe Russo
  Genre: Action, Adventure, Drama
  score: 8.6
  [INFO] Success to insert!
- Plain: Terminal output
- Bold: User input
- Hint: use a input() function

Confirm that the insertion is operated properly.
Print only movie’s title(s)

[INFO] Movie list
Avengers: Infinity War

Insert three movies more. Use insert_many
- Movie Title: Bohemian Rhapsody
  Director: Bryan Singer
  Genre: Biography, Drama, Music
  score: 8.1
  Quit (Y/n)? n
- Movie Title: Happy Death Day 2U
  Director: Christopher Landon
  Genre: Drama, Horror, Mystery
  score: 6.6
  Quit (Y/n)? n
- Movie Title: Spider-Man: Into the Spider-Verse
  Director: Bob Persichetti, Peter Ramsey
  Genre: Animation, Action, Adventure
  score: 8.6
  Quit (Y/n)? Y
  [INFO] Success to insert!
Confirm that the insertion is operated properly.
Print only movie’s title(s)

[INFO] Movie list
Avengers: Infinity War
Bohemian Rhapsody
Happy Death Day 2U
Spider-Man: Into the Spider-Verse

Update movie information
- Querying movie title and update the information
- [INFO] Enter the information which you want to edit
  Movie Title: Avengers: Infinity War
  Director: Anthony Russo and Joe Russo
  Genre: Action, Adventure, Drama
  score: 8.6
  [INFO] Success to update!
- Plain: Terminal output
- Bold: query
- Italic: update
Print all documents

[INFO] Movie list
[{'_id': ObjectId('5c76acb565d1016cd33d4a5d'),
  'director': 'Anthony Russo and Joe Russo',
  'genre': ['Action', ' Adventure', ' Drama'],
  'score': 8.6,
  'title': 'Avengers: Infinity War'},
 {'_id': ObjectId('5c76ad0d65d1016cd33d4a5e'),
  'director': 'Bryan Singer',
  'genre': ['Biography', ' Drama', ' Music '],
  'score': 8.1,
  'title': 'Bohemian Rhapsody'},
 {'_id': ObjectId('5c76ad0d65d1016cd33d4a5f'),
  'director': 'Christopher Landon',
  'genre': ['Drama', ' Horror', ' Mystery'],
  'score': 6.6,
  'title': 'Happy Death Day 2U'},
 {'_id': ObjectId('5c76ad0d65d1016cd33d4a60'),
  'director': 'Bob Persichetti, Peter Ramsey',
  'genre': ['Animation', ' Action', ' Adventure'],
  'score': 8.6,
  'title': 'Spider-Man: Into the Spider-Verse'}]

Remove movie (Plain: Output, Bold: User input)
Remove a document
- [INFO] Enter the title which you want to remove
  Movie Title: Happy Death Day 2U
  [INFO] Are you sure? (Y/n) Y
  [INFO] Deleted
Result

[INFO] Movie list
Avengers: Infinity War
Bohemian Rhapsody
Spider-Man: Into the Spider-Verse

Index

Exercise 1

Create an index in ascending order for the writer field and get the index information.

{'_id_': {'key': [('_id', 1)], 'ns': 'lab6.blog', 'v': 2},
'writer_1': {'key': [('writer', 1)], 'ns': 'lab6.blog', 'v': 2}}

Again, print a query plan using executionStats corresponding to the query that finds a writer, Kim.

 "executionStats" : {
…
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 1,
                "totalDocsExamined" : 1,
                "nReturned": 1,
                "executionStages": {
                              "inputStage": {
                                         "stage": "IXSCAN",
…

Create the compound index on id in descending order and writer in ascending order.

{'_id_': {'key': [('_id', 1)], 'ns': 'lab6.blog', 'v': 2}, 
'id_-1_writer_1': {'key': [('id', -1), ('writer', 1)], 'ns': 'lab6.blog', 'v': 2}, 
'writer_1': {'key': [('writer', 1)], 'ns': 'lab6.blog', 'v': 2}}

Drop all indexes on the blog collection.

{'_id_': {'key': [('_id', 1)], 'ns': 'lab6.blog', 'v': 2}}

Exercise 2

Index 생성
- Create a unique index to doc_id in ascending order
- Try to build a unique index to line_num
- Create a sparse index to intersect in ascending order
- Create a unique and sparse index to intersect_id in ascending order
- Check the index information

Compare the results below two cases. (metro collection)

Show the first two documents, sorting with doc_id in ascending order.
Using intersect index by hint(), show the first two documents
Sort doc_id in ascending order
Use explain() above each case (executionStats).

No Index

 "executionStats" : {
…
	 'executionTimeMillis': 155,
	 'nReturned': 2,
	 'totalDocsExamined': 186024,
	 'totalKeysExamined': 0
	}

Indexed

 "executionStats" : {
…
	'executionTimeMillis': 21,
	'nReturned': 2,
	'totalDocsExamined': 2,
	'totalKeysExamined': 2
	}

GeoSpatial Index

Exercise 1

Create a geospatial index (2dsphere) for the states collection

Get information on states collection’s indexes

{'_id_': {'key': [('_id', 1)], 'ns': 'geo.states', 'v': 2}, 
'loc_2dsphere': 
{'2dsphereIndexVersion': 3, 'key': [('loc', '2dsphere')], 'ns': 'geo.states', 'v': 2}}

Remove the geospatial index from the states collection
- Get information on states collection’s indexes
  {'_id_': {'v': 2, 'key': [('_id', 1)], 'ns': ‘geo.states'}}

Exercise 2

Use the restaurant collection in the geo database

Create a valid query and print the query plan using executionStats•Find documents between '10200‘ and '10280‘ in zipcode
Create an index to zipcode (Hint: Embedded Document)
Execute #1, again.

No Index

  "executionStats" : {
 …
                "executionTimeMillis" : 11,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 25359,
                "nReturned": 21,
…

Indexed

 "executionStats" : {
…
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 21,
                "totalDocsExamined" : 21,
                "nReturned": 21,
…

Use the restaurant collection in the geo database

Create a query and print the query plan using executionStats•Find documents that have at least the grade is ‘B’ in the grades.grade field.
Create an index to grade (Hint: Indexing on Array)
Execute #1, again.

No Index

 "executionStats" : {
…
                "executionTimeMillis" : 17,
                "totalKeysExamined" : 0,
                "totalDocsExamined" : 25359,
                "nReturned": 8280,
…

Indexed

 "executionStats" : {
…
                "executionTimeMillis" : 13,
                "totalKeysExamined" : 8280,
                "totalDocsExamined" : 8280,
                "nReturned": 8280,
…

Exercise 3

Find international airports in the state that has the above zip code (10044)

Sorting the name in ascending order

Print state’s name and airport name each.

Hint: states and small_zip collections

[{'name': 'Albany County'}, 
{'name': 'Greater Buffalo Intl'}, 
{'name': 'Greater Rochester International'}, 
{'name': 'John F Kennedy Intl'}, 
{'name': 'La Guardia'}, 
{'name': 'Massena Intl'}, 
{'name': 'Niagara Falls Intl'}, 
{'name': 'Ogdensburg Intl'}, 
{'name': 'Stewart Intl'}, 
{'name': 'Syracuse Hancock Intl'}, 
{'name': 'Watertown International'}]

Find Korean restaurants that are located at less 2km from international airports.
- Warn: should create 2dsphere index to address for restaurants collection.

Find states that are intersect to the flight path (trajectory).
- Print names including source and destination states.

Make Geojson format whose type is LineString.
- Find the two coordinates (LAX, DTW) Result

Result

[{'name': 'Arizona'},
{'name': 'California'},
{'name': 'Colorado'},
{'name': 'Illinois'},
{'name': 'Iowa'},
{'name': 'Kansas'},
{'name': 'Michigan'},
{'name': 'Nebraska'}, 
'name': 'Nevada'},
{'name': 'Utah'}]

Exercise 4

Using the restaurants collection.
Find restaurants whose names include "Kimchi".

Print only name, zipcodeand address

{ "zipcode" : 11231, "address" : "Smith Street 478" }
{ "zipcode" : 11238, "address" : "Washington Avenue 766" }
{ "zipcode" : 10036, "address" : "West 48 Street 18-20" }

Using the restaurants and states collections.
Find restaurants that have "NolbuRestaurant＂(restaurants collection)
Print the state (states collection)
```
New York
```

Using restaurants.json
Among the "Korean" restaurants, find a restaurant that has "Ramen"but "Izakaya“.

Then, find a restaurant near above restaurant in 150m.

{ '_id' : ObjectId('5c8626739f44c9f6768035da'),
  'address' : {'building' : '8', 'coord': [-73.9916305, 40.7247106],
               'street': 'Extra Pl', 'zipcode': '10003'},
  'borough': 'Manhattan',
  'cuisine': 'Japanese',
  'grades': [{'date': datetime.datetime(2015, 1, 20, 0, 0),
               'grade': 'Not Yet Graded',
               'score': 21}],
  'name': 'Ko Ep, Llc',
  'restaurant_id': '50015854'}

Text Index

Exercise 1

Using restaurants
Build a text index to the 'name' field.
Find restaurants whose names include 'Kyochon‘ Use text index

Print only borough and name fields

{ "borough" : "Queens", "name" : "Kyochon Chicken" }
{ "borough" : "Manhattan", "name" : "Kyochon" }

Exercise 2

Take a look the index information in map and countries collections.
Find universities in Korea.
- Get geometry information in the countries collection.
- keyword -properties.admin: “South Korea”

Return ten documents with sorting by “properties.name”, and show the name only.

Note that there is no ‘properties.name’ in a document; therefore, remove the document.

[{'properties': {'name': 'GIST 광주 과학 기술원'}},
 {'properties': {'name': 'ICT폴리택대학'}},
 {'properties': {'name': 'KAIST 문지 캠퍼스'}},
 {'properties': {'name': 'KC대학교'}},
 {'properties': {'name': 'KT원주연수원'}},
 {'properties': {'name': 'U1대학교 아산캠퍼스'}},
 {'properties': {'name': '가천대학교 글로벌캠퍼스'}},
 {'properties': {'name': '가천대학교 메디컬캠퍼스'}},
 {'properties': {'name': '가톨릭관동대학교'}},
 {'properties': {'name': '가톨릭대학교 성신교정'}}]

Search the ‘properties.name’ that includes ‘서울대입구’
Using regular expression ($regex) or a text index.

Sort properties.name in ascending order and display only a name field as below.

[{'properties': {'name': '서울대입구 삼성아파트'}},
 {'properties': {'name': '서울대입구 풍림아이원 1단지'}},
 {'properties': {'name': '서울대입구 풍림아이원 2단지'}}]

Aggregation Framework

Exercise 1

Use restaurants.json

Using aggregation framework, print documents below.

1. Find Korean cuisine

2. Group by borough and count the cuisines.

[{'_id': 'Brooklyn', 'count': 16},
{'_id': 'Queens', 'count': 167},
{'_id': 'Staten Island', 'count': 1},
{'_id': 'Manhattan', 'count': 78}]

Exercise 2

Using aggregation framework, print documents below.

1. Find Korean cuisine

2. Unwind grades

3. Group by borough and grade in grades, and count the document

4. Sort by count and print five documents

[{'_id': {'borough': 'Queens', 'grade': 'A'}, 'count': 443},
{'_id': {'borough': 'Manhattan', 'grade': 'A'}, 'count': 232},
{'_id': {'borough': 'Queens', 'grade': 'B'}, 'count': 140},
{'_id': {'borough': 'Manhattan', 'grade': 'B'}, 'count': 49},
{'_id': {'borough': 'Brooklyn', 'grade': 'A'}, 'count': 38}]

Exercise 3

Find international airports in California using aggregate framework

states collection
- Get the California location (Polygon)
airports collection
- Find “intl” in name field (text)
- Find airports in the California
- Project name, type and code (w/o _id)
- Sort by name (ascending order) and code (descending order)

Exercise 4

Find Korean restaurants that are located at less 2km from international airports (use the type field).

Warn: should create 2dsphere index to address for restaurants collection.

[{'_id': ObjectId('5c8626729f44c9f6767fe0af'),
'address': {'building': '0',
             'coord': 497.9151037865528,
             'street': 'Jfk International Airpor',
             'zipcode': '11430'},
 'borough': 'Queens',
 'cuisine': 'Korean',
 'grades': [{'date': datetime.datetime(2014, 6, 18, 0, 0),
             'grade': 'A',
             'score': 2},
            {'date': datetime.datetime(2014, 1, 14, 0, 0),
             'grade': 'A',
             'score': 12},
            {'date': datetime.datetime(2013, 1, 4, 0, 0),
             'grade': 'A',
             'score': 11},
            {'date': datetime.datetime(2012, 4, 25, 0, 0),
             'grade': 'A',
             'score': 13}],
 'name': 'Korean Lounge',
 'restaurant_id': '40625198'}]

Exercise 5

Print the average, minimum, and maximum of passengers(ride_pasgr_numfield) of each subway line.
Hint: group by the ‘line_num’ field and use $max, $min, and $avg.

Result

[{'_id': '경원선', 'avg_station': 7413.872106666667, 'max_station': 27076, 'min_station': 1},
{'_id': '일산선', 'avg_station': 11100.640561622466, 'max_station': 25857, 'min_station': 1},
{'_id': '1호선', 'avg_station': 26936.31375, 'max_station': 70147, 'min_station': 4815},
...
{'_id': '수인선', 'avg_station': 3436.322596153846, 'max_station': 9023, 'min_station': 25},
{'_id': '경강선', 'avg_station': 2294.190909090909, 'max_station': 9201, 'min_station': 192}]

Exercise 6

Print the total number of passengers (ride_pasgr_numfield) during 2017-12-21 ~ 2017-12-28.
‘use_dt’ is a stringtype.
Hint: use $match,$gt$lt, $sum, $sort

Result

[{'_id': {'use_dt': '20171221'}, 'total_rider_pasgr': 8505895},
{'_id': {'use_dt': '20171222'}, 'total_rider_pasgr': 8917017},
{'_id': {'use_dt': '20171223'}, 'total_rider_pasgr': 6781294},
{'_id': {'use_dt': '20171224'}, 'total_rider_pasgr': 4866330},
{'_id': {'use_dt': '20171225'}, 'total_rider_pasgr': 5096990},
{'_id': {'use_dt': '20171226'}, 'total_rider_pasgr': 7935515},
{'_id': {'use_dt': '20171227'}, 'total_rider_pasgr': 7906780},
{'_id': {'use_dt': '20171228'}, 'total_rider_pasgr': 8056509}]

Previous집계 프레임워크 (Aggregation Framework)Next관리 (Administration)

Last updated 5 years ago