1 of 64

Mongo DB II

October 31, 2023

Data 101, Fall 2023 @ UC Berkeley

Lisa Yan https://fa23.data101.org/

1

LECTURE 20

2 of 64

Join at slido.com�#MongoDB

ⓘ

Click Present with Slido or install our Chrome extension to display joining instructions for participants while presenting.

3 of 64

Onto Aggregation Queries

There are three main types of query in the MongoDB Query Language, or MQL:

  1. Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  2. Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case.
  3. Update queries

3

Aggregation queries are composed of a linear pipeline of stages.

  • Each stage manipulates the output collection�of the prior stage in some way.
  • Note aggregation queries do not modify�the input collection! (more: see Update queries)

✅

like selection/projection in .find() retrieval queries, but more expressive

new

collection.aggregate ( [

{ $stage1op: {…} },

{ $stage2op: {…} },

…

{ $stageNop: {…} }

] )

Aggregation Pipeline Operators include:

  • $match
  • $project
  • $sort/$limit
  • $group
  • $unwind
  • $lookup

​

#MongoDB

4 of 64

[Summary so far] MQL: MongoDB Query Language

All queries are invoked as db.collection.operation1(...).operation2(...)

  • Object-oriented and pipelined
    • “input”: a name of a collection (e.g., named collection) in database db
    • output: collection
  • Looks like pandas DataFrames, not SQL!

There are three main types of query in the MongoDB Query Language, or MQL:

  • Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  • Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case
  • Update queries

4

#MongoDB

5 of 64

[Summary so far] Retrieval Queries

Retrieval queries are called via methods on a specific document collection within a database.

  • Returns documents (or single one, as in find_one() that match <predicate>.
  • Optional: keep fields as specified in <projection>.
  • Parameter types: both <predicate> and <projection> expressed as objects.

To return all documents in the collection: collection.find({})

5

# list one document in the output collection�collection.find_one(predicate, projection)

# list all in the output collection�collection.find(predicate, projection)

#MongoDB

6 of 64

[Summary so far] Retrieval Queries

(db.prizes.find({category: "peace"},

{_id: 0, category: 1, year: 1,

"laureates.firstname": 1,

"laureates.surname": 1})

.sort({year: 1, category: -1})

.limit(2))

Retrieval queries are called via methods on a specific document collection within a database.

  • Returns documents (or single one, as in find_one() that match <predicate>.
  • Optional: keep fields as specified in <projection>.
  • Parameter types: both <predicate> and <projection> expressed as objects.

To return all documents in the collection: collection.find({})

(db.prizes.find({"category": "peace"},

{"_id": 0, "category": 1, "year": 1,

"laureates.firstname": 1,

"laureates.surname": 1})

.sort([("year", 1), ("category", -1)])

.limit(2))

6

# list one document in the output collection�collection.find_one(predicate, projection)

# list all in the output collection�collection.find(predicate, projection)

find(): SELECT <projection>

FROM collection

WHERE <predicate>

sort(): ORDER BY

limit(): LIMIT

​

(Note that pymongo’s syntax is slightly different)

MQL official syntax

(more in extra slides)

#MongoDB

7 of 64

Aggregation Queries

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

7

Lecture 20, Data 101 Fall 2023

8 of 64

Onto Aggregation Queries

There are three main types of query in the MongoDB Query Language, or MQL:

  • Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  • Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case.
  • Update queries

8

✅

#MongoDB

9 of 64

Onto Aggregation Queries

There are three main types of query in the MongoDB Query Language, or MQL:

  • Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  • Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case
  • Update queries

9

Aggregation Pipeline Operators include:

  • $match
  • $project
  • $sort/$limit
  • $group
  • $unwind
  • $lookup

​

Aggregation queries are composed of a linear pipeline of stages.

  • Each stage manipulates the output collection�of the prior stage in some way.
  • Note aggregation queries do not modify�the input collection! (more: see Update queries)

✅

collection.aggregate ( [

{ $stage1op: {…} },

{ $stage2op: {…} },

…

{ $stageNop: {…} }

] )

Let’s start with this one, which is generally what we think of when we think of aggregation in SQL.

#MongoDB

10 of 64

New database: MongoDB zipcodes

Using the zipcodes, find states with population >15M and list the state, population in descending order.

How would we do this in SQL?

SELECT state AS id,

SUM(pop) AS totalPop

FROM zips

GROUP BY state

HAVING totalPop >= 15000000

ORDER BY totalPop DESC;

10

[Mongo tutorial]

29353 zipcodes (one document per zipcode)

{ "_id": "CA", "totalPop": 29754890 }

{ "_id": "NY", "totalPop": 17990402 }

{ "_id": "TX", "totalPop": 16984601 }�...

#MongoDB

11 of 64

From GROUP BY/HAVING to .aggregate()

Using the zipcodes, find states with population >15M and list the state, population in descending order.

SELECT state AS id,

SUM(pop) AS totalPop

FROM zips

GROUP BY state

HAVING totalPop >= 15000000

ORDER BY totalPop DESC;

11

{ "_id": "CA", "totalPop": 29754890 }

{ "_id": "NY", "totalPop": 17990402 }

{ "_id": "TX", "totalPop": 16984601 }�...

1b aggregate

​

1 group

2 “selection”

3 sort

$match

$group

$sort

MongoDB $group docs

#MongoDB

12 of 64

From GROUP BY/HAVING to .aggregate()

Using the zipcodes, find states with population >15M and list the state, population in descending order.

SELECT state AS id,

SUM(pop) AS totalPop

FROM zips

GROUP BY state

HAVING totalPop >= 15000000

ORDER BY totalPop DESC;

db.zips.aggregate( [

{ $group: { _id: "$state", � totalPop:� {$sum: "$pop" } } },

{ $match: { totalPop:� { $gte: 15000000 } } },

{ $sort : { totalPop : -1 } }

] )

12

{ "_id": "CA", "totalPop": 29754890 }

{ "_id": "NY", "totalPop": 17990402 }

{ "_id": "TX", "totalPop": 16984601 }�...

1b aggregate

​

1 group

2 “selection”

3 sort

$match

$group

$sort

MongoDB $group docs

#MongoDB

13 of 64

(1/3) $group syntax, detailed

Recall that a valid collection needs to have an _id.

$group syntax therefore specifies:

  • The _id, which are the attributes to group by.
  • Additional fields as aggregation functions.

db.zips.aggregate( [

{ $group: { _id: "$state", � totalPop:� {$sum: "$pop" } } },

{ $match: { totalPop:� { $gte: 15000000 } } },

{ $sort : { totalPop : -1 } }

] )

13

  • .aggregate() takes an array of pipeline stages.
  • Each stage in the pipeline “outputs” a valid collection to “input” into the next stage.

MongoDB $group docs

#MongoDB

14 of 64

(2/3) Two uses of the dollar ($) symbol

Recall that a valid collection needs to have an _id.

$group syntax therefore specifies:

  • The _id, which are the attributes to group by.
  • Additional fields as aggregation functions.

db.zips.aggregate( [

{ $group: { _id: "$state", � totalPop:� {$sum: "$pop" } } },

{ $match: { totalPop:� { $gte: 15000000 } } },

{ $sort : { totalPop : -1 } }

] )

14

Each stage in the pipeline “outputs” a valid collection to “input” into the next stage.

Note the the two uses of $!

  • Mongo Query Language (MQL) keywords,�e.g., stage names, agg func names; and
  • Attribute names on the value side�of field-value pairs. Note the quotes!

MongoDB $group docs

#MongoDB

15 of 64

(3/3) Accumulator Operators

Recall that a valid collection needs to have an _id.

$group syntax therefore specifies:

  • The _id, which are the attributes to group by.
  • Additional fields as aggregation functions.

db.zips.aggregate( [

{ $group: { _id: "$state", � totalPop:� {$sum: "$pop" } } },

{ $match: { totalPop:� { $gte: 15000000 } } },

{ $sort : { totalPop : -1 } }

] )

15

Each stage in the pipeline “outputs” a valid collection to “input” into the next stage.

Note the the two uses of $!

  • Mongo Query Language (MQL) keywords,�e.g., stage names, agg func names; and
  • Attribute names on the value side�of field-value pairs. Note the quotes!

$group stage Accumulator Operators:

  • $sum, $avg, $max are standard, plus:
  • $first: first expression value per group
    • e.g., if performed after sort, then docs are in a specific order.
  • $push: array of expression values per group
    • Not possible in relational context, where values are atomic!
  • $addToSet: like $push, but eliminates duplicates

MongoDB $group docs

#MongoDB

16 of 64

Unwind

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

16

Lecture 20, Data 101 Fall 2023

17 of 64

Onto Aggregation Queries

There are three main types of query in the MongoDB Query Language, or MQL:

  • Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  • Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case
  • Update queries
  • $unwind “unrolls” arrays, similar to pd.melt().
  • $lookup performs a (somewhat gross) left outer equi-join.

17

Aggregation queries are composed of a linear pipeline of stages.

  • Each stage manipulates the output collection�of the prior stage in some way.
  • Note aggregation queries do not modify�the input collection! (more: see Update queries)

✅

like selection/projection in .find() retrieval queries, but more expressive

collection.aggregate ( [

{ $stage1op: {…} },

{ $stage2op: {…} },

…

{ $stageNop: {…} }

] )

Stages we will cover:

  • $match
  • $project
  • $sort/$limit
  • $group
  • $unwind
  • $lookup

​

#MongoDB

18 of 64

Panic! At the Disco Database Inventory

Retail inventory

  • One document per item
  • Various tags (e.g., for search)
  • Various in-stock locations

18

Loosely based off of this Mongo tutorial

#MongoDB

19 of 64

$unwind

Unwind expands an array by constructing documents one per element of the array.

19

db.inventory.aggregate( [

{ $unwind : "$tags" },

{ $project : {_id : 0, instock: 0}}

] )

MongoDB $unwind docs

#MongoDB

20 of 64

$unwind

Unwind expands an array by constructing documents one per element of the array.

20

db.inventory.aggregate( [

{ $unwind : "$tags" },

{ $project : {_id : 0, instock: 0}}

] )

No relational model analog! Closer to the pd.melt() from DataFrames.

Impossible to do in a traditional relational model, as relational values are atomic (i.e., no arrays).

  • (note: some RDBMSes nowadays do indeed support arrays, e.g., Postgres link).

MongoDB $unwind docs

#MongoDB

21 of 64

[Exercise] A common template with $unwind

Suppose we want to find the total quantity per item across location.

21

$group

$unwind

db.inventory.aggregate(

[{$unwind: "$instock"}, { $group: ??? }])

#MongoDB

22 of 64

[Exercise] A common template with $unwind

Suppose we want to find the total quantity per item across location.

22

db.inventory.aggregate(

[{$unwind: "$instock"}, { $group: ??? }])

A. { $group: [_id, "$item", totalqty, $sum: "$instock.qty" ]}

B. { [ $group: _id, "$item", totalqty, $sum, "$instock.qty" ]}

C. { $group: [{_id: "$item"}, {totalqty: {$sum: "$instock.qty"}} ]}

D. { $group: { _id: "$item", totalqty: { $sum: "$instock.qty" } }}

E. Something else

🤔

What replaces the aggregate pipeline stage�{ $group: ??? }?

#MongoDB

23 of 64

What replaces the aggregate pipeline stage { $group: ??? }?

ⓘ

Click Present with Slido or install our Chrome extension to activate this poll while presenting.

24 of 64

[Exercise] A common template with $unwind

Suppose we want to find the total quantity per item across location.

24

db.inventory.aggregate(

[{ $unwind: "$instock" },

{ $group: {

_id: "$item",

totalqty: { $sum: "$insto]ck.qty" }

}

}

] )

$group

_id

totalqty

The value for a $group stage is a collection of agg. attributes to return per document in the output collection.

D.

#MongoDB

25 of 64

Lookup

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

25

Lecture 20, Data 101 Fall 2023

26 of 64

$lookup

Conceptually, $lookup performs the following�for each document:

  • Find documents from the other collection
    • local field must match foreign field exactly
    • place each of the matches in an array

26

{ $lookup: {

from: <collection to join>,

localField: <referencing field>,

foreignField: <referenced field>,

as: <output array field>

} }

db.inventory.aggregate( [

{ $lookup : {from : "inventory",

localField : "instock.loc",

foreignField : "instock.loc",

as :"otheritems"}},

{ $project : {_id : 0,

tags : 0,

dim : 0,� "otheritems._id": 0} }

] )

MongoDB $lookup docs

#MongoDB

27 of 64

$lookup

Conceptually, $lookup performs the following�for each document:

  • Find documents from the other collection
    • local field must match foreign field exactly
    • place each of the matches in an array

27

{ $lookup: {

from: <collection to join>,

localField: <referencing field>,

foreignField: <referenced field>,

as: <output array field>

} }

🤔

db.inventory.aggregate( [

{ $lookup : {from : "inventory",

localField : "instock.loc",

foreignField : "instock.loc",

as :"otheritems"}},

{ $project : {_id : 0,

tags : 0,

dim : 0,� "otheritems._id": 0} }

] )

A. Inner equijoin

B. Inner natural join

C. Left outer equijoin

D. Left outer natural join

E. Right outer equijoin

F. Right outer natural join

G. Something else

What type of join is $lookup, effectively?

MongoDB $lookup docs

#MongoDB

28 of 64

What type of join is $lookup, effectively?

ⓘ

Click Present with Slido or install our Chrome extension to activate this poll while presenting.

29 of 64

$lookup

Conceptually, $lookup performs the following�for each document:

  • Find documents from the other collection
    • local field must match foreign field exactly
    • place each of the matches in an array

29

{ $lookup: {

from: <collection to join>,

localField: <referencing field>,

foreignField: <referenced field>,

as: <output array field>

} }

db.inventory.aggregate( [

{ $lookup : {from : "inventory",

localField : "instock.loc",

foreignField : "instock.loc",

as :"otheritems"}},

{ $project : {_id : 0,

tags : 0,

dim : 0,� "otheritems._id": 0} }

] )

MongoDB $lookup docs

#MongoDB

30 of 64

$lookup, A Common Use Case

30

db.inventory.aggregate( [

{ $lookup : {from : "inventory",

localField : "instock.loc",

foreignField : "instock.loc",

as :"otheritems"}},

{ $project : {_id : 0,

tags : 0,

dim : 0,� "otheritems._id": 0} }

] )

Q: What is this actually doing?

A: Use a self-join to find, for each item, other items in the same location.

One document in the output collection:

MongoDB $lookup docs

#MongoDB

31 of 64

[Matching Exercise] Aggregation Pipeline Operators

1. $match

​

2. $project

​

3. $sort

​

4. $limit

​

5. $group

​

6. $unwind

​

7. $lookup

A. WHERE / HAVING, depending on the � pipeline stage order

B. SELECT / relational algebra selection projection

C. GROUP BY

D. agg functions like MAX(),SUM(), etc.

E. pd.melt()

F. ORDER BY

G. LIMIT

H. NATURAL JOIN

I. LEFT JOIN /* equi */

31

🤔

Match each item on the left with the choice(s) on the right. An item can matches to be multiple choices! Not all choices have a match.

Format: 1-AB, etc.

#MongoDB

32 of 64

Match each item on the left with the choice(s) on the right. An item can matches to be multiple choices! Not all choices have a match.

Format: 1-AB, etc.

ⓘ

Click Present with Slido or install our Chrome extension to activate this poll while presenting.

33 of 64

[Matching Exercise] Aggregation Pipeline Operators

1. $match

​

2. $project

​

3. $sort

​

4. $limit

​

5. $group

​

6. $unwind

​

7. $lookup

A. WHERE / HAVING, depending on the � pipeline stage order

B. SELECT / relational algebra selection projection

C. GROUP BY

D. agg functions like MAX(),SUM(), etc.

E. pd.melt()

F. ORDER BY

G. LIMIT

H. NATURAL JOIN

I. LEFT JOIN /* equi */

33

#MongoDB

34 of 64

Demo: Multiple-Attribute Grouping

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

34

Lecture 20, Data 101 Fall 2023

35 of 64

// not in ipynb, but useful for you to know

db.zips.count_documents({})

35

#MongoDB

36 of 64

1. What is this doing?

db.zips.aggregate( [   

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },   

{ $group: { _id: "$_id.state", avgCityPop: { $avg: "$pop" } } }

] )

36

Demo

#MongoDB

37 of 64

1. What is this doing?

db.zips.aggregate( [   

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },   

{ $group: { _id: "$_id.state", avgCityPop: { $avg: "$pop" } } }

] )

37

Find average population per city per state.

  • Two group stages!
  • $ values refer to previously defined attributes.
  • Why does “$_id.state” work?

group

group

Demo

#MongoDB

38 of 64

2. What is this doing?

db.zips.aggregate( [

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },

{ $sort: { pop: -1 } },

{ $group: { _id : "$_id.state", bigCity: { $first: "$_id.city" }, bigPop: { $first: "$pop" } } },

{ $sort : {bigPop : -1} },

{ $project : {bigPop : 0} }

] )

38

Demo

#MongoDB

39 of 64

2. What is this doing?

db.zips.aggregate( [

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },

{ $sort: { pop: -1 } },

{ $group: { _id : "$_id.state", bigCity: { $first: "$_id.city" }, bigPop: { $first: "$pop" } } },

{ $sort : {bigPop : -1} },

{ $project : {bigPop : 0} }

] )

​

39

group

sort

group

sort

project

Find the biggest city per state.

Demo

#MongoDB

40 of 64

3. What is this doing?

db.zips.aggregate( [

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },

{ $sort: { pop: -1 } },

{ $group: { _id : "$_id.state", bigCity: { $first: "$_id.city" }, bigPop: { $first: "$pop" } } },

{ $sort : {bigPop : -1} },

{ $project : { _id : 0, state : "$_id", bigCityDeets: { name: "$bigCity", pop: "$bigPop" } } }

] )

​

​

​

40

Demo

#MongoDB

41 of 64

3. What is this doing?

db.zips.aggregate( [

{ $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } },

{ $sort: { pop: -1 } },

{ $group: { _id : "$_id.state", bigCity: { $first: "$_id.city" }, bigPop: { $first: "$pop" } } },

{ $sort : {bigPop : -1} },

{ $project : { _id : 0, state : "$_id", bigCityDeets: { name: "$bigCity", pop: "$bigPop" } } }

] )

​

​

​

41

Unlike in “vanilla” projection with find(), $project in aggregate pipelines can construct new nested documents in the output!

Nest the name of the city and population into a nested doc.

Demo

#MongoDB

42 of 64

Update Queries

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

42

Lecture 20, Data 101 Fall 2023

43 of 64

Onto Aggregation Queries

There are three main types of query in the MongoDB Query Language, or MQL:

  • Retrieval queries: Restricted queries of the form SELECT-WHERE-ORDER-BY-LIMIT
  • Aggregation queries: A general pipeline of operators
    • A bit of a misnomer; can capture retrievals as a special case
  • Update queries

43

✅

✅

#MongoDB

44 of 64

Update Queries

insertOne() insertMany()

deleteOne() deleteMany()

updateOne() updateMany()

44

pymongo note:

insert_one() insert_many()�etc.

We will focus on the Many() case, since it is more general.

for One(), read the docs.

#MongoDB

45 of 64

insertMany()

Several actions will be taken as part of this insert:

  • Will create the inventory collection if absent from the database.
  • Will add the _id attrib to each document�(since it isn’t specified here).
    • By default, _id will be the first field for each document.

​

​

45

MongoDB docs

required parameter: array of docs

optional parameters (next slide)

Unlike SQL: To insert new documents in MongoDB, we don’t need to specify the schema beforehand via DDL (Data Definition Language)!

#MongoDB

46 of 64

insertMany(), Optional parameters

Several actions will be taken as part of this insert:

  • Will create the inventory collection if absent from the database.
  • Will add the _id attrib to each document�(since it isn’t specified here).
    • By default, _id will be the first field for each document.

​

​

46

MongoDB docs

required parameter: array of docs (prev slide)

optional parameters

Unlike SQL: To insert new documents in MongoDB, we don’t need to specify the schema beforehand via DDL (Data Definition Language)!

What are transactions? More next time!!

(more at this link)

#MongoDB

47 of 64

deleteMany() and updateMany()

db.collection.deleteMany(� {<filter>})

db.collection.updateMany ( � {<filter>},� {<update>})

​

47

SQL analogy:�UPDATE Relation SET <update> WHERE <filter>

MongoDB docs: deleteMany(), updateMany()

#MongoDB

48 of 64

Example: updateMany()

db.collection.deleteMany(� {<filter>})

db.collection.updateMany ( � {<filter>},� {<update>})

​

48

MongoDB docs: deleteMany(), updateMany()

db.inventory.updateMany ( � {"dim.0": { $lt: 15 } }, � { $set: { "dim.0": 15,

status: "InvalidWidth"} }

)

db.inventory.updateMany (

{"dim.0": { $lt: 15 } },

{ $inc: { "dim.0": 5},

$set: {status: "InvalidWidth"} }

)

1.

​

​

​

​

​

2.

Match documents whose dim0 < 15.

Example document:

{ "item": "journal",

"tags": ["blank", "red"],

"dim": [ 14, 21 ],

"instock": [ { "loc": "A", "qty": 5 },

{ "loc": "C", "qty": 15 } ]

}

{<filter>} matches retrieval query arguments (docs).

#MongoDB

49 of 64

Example: updateMany()

db.collection.deleteMany(� {<filter>})

db.collection.updateMany ( � {<filter>},� {<update>})

​

49

MongoDB docs: deleteMany(), updateMany()

🤔

db.inventory.updateMany ( � {"dim.0": { $lt: 15 } }, � { $set: { "dim.0": 15,

status: "InvalidWidth"} }

)

db.inventory.updateMany (

{"dim.0": { $lt: 15 } },

{ $inc: { "dim.0": 5},

$set: {status: "InvalidWidth"} }

)

1.

​

​

​

​

​

2.

A. Set dim0 to 5

B. Set dim0 to 15

C. Increment dim0 by 5

D. Increment dim0 by 15

E. Set status to invalidWidth

How are the relevant documents updated? Select all that apply.

Format: 1-AB, etc.

Match documents whose dim0 < 15.

Example document:

{ "item": "journal",

"tags": ["blank", "red"],

"dim": [ 14, 21 ],

"instock": [ { "loc": "A", "qty": 5 },

{ "loc": "C", "qty": 15 } ]

}

#MongoDB

50 of 64

How are the relevant documents updated? Select all that apply.

Format: 1-AB, etc.

ⓘ

Click Present with Slido or install our Chrome extension to activate this poll while presenting.

51 of 64

Example: updateMany()

db.collection.deleteMany(� {<filter>})

db.collection.updateMany ( � {<filter>},� {<update>})

​

51

Match documents whose dim0 < 15.

MongoDB docs: deleteMany(), updateMany()

db.inventory.updateMany ( � {"dim.0": { $lt: 15 } }, � { $set: { "dim.0": 15,

status: "InvalidWidth"} }

)

db.inventory.updateMany (

{"dim.0": { $lt: 15 } },

{ $inc: { "dim.0": 5},

$set: {status: "InvalidWidth"} }

)

1.

​

​

​

​

​

2.

Example document:

{ "item": "journal",

"tags": ["blank", "red"],

"dim": [ 14, 21 ],

"instock": [ { "loc": "A", "qty": 5 },

{ "loc": "C", "qty": 15 } ]

}

B. Set dim0 to 15

E. Set status to invalidWidth

C. Increment dim0 by 5

E. Set status to invalidWidth

{<update>} matches aggregation query arguments (docs).

#MongoDB

52 of 64

MongoDB Summary

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

52

Lecture 20, Data 101 Fall 2023

53 of 64

Summary

MongoDB has evolved into a mature data system with some different design decisions, and relearning many of the canonical relational database lessons.

MongoDB has a flexible data model and a powerful (if confusing) query language.

​

53

Many of the internal design decisions as well as the query & data model can be understood when compared with what we know.

  • Database systems provide a good "gold standard" to compare against.
  • Some examples on the next slide!

#MongoDB

54 of 64

[Sneak Peek] MongoDB Internals in One Slide

The MongoDB aggregation pipeline uses various optimization heuristics. Here a few w/$match:

  • If early in the pipeline, will use indexes (which users can explicitly declare).
  • Selection fusion: $matches will be merged together if possible
  • Selection pushdown: Sometimes will be used, but not always
    • e.g., will not push before $lookup.

No cost-based optimization as far as one can tell.

54

Bizarre constraint: Intermediate results of aggregations must not be too large.

  • Limit: 100MB, otherwise will end up spilling to disk
  • Not particularly clear if there is pipelining across aggregation operators/stages.

​

More here.

​

​

#MongoDB

55 of 64

[Extra] Aggregation Query Demos

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

55

Lecture 20, Data 101 Fall 2023

56 of 64

What do the following queries do? Part 1

db.prizes.aggregate([{$group: {_id: "$category", awardyears: {$sum : 1}}}]) // A

db.prizes.aggregate([{$group: {_id: "$category", awardyears: {$sum : 1}}}, {$match : {awardyears: {$lt: 100}}}]) // B

db.prizes.aggregate([{$group: {_id: "$category", awardyears: {$sum : 1}}}, {$match : {awardyears: {$lt: 100}}}, {$project : {_id: 0, awardyears: 1}}]) // C

db.prizes.aggregate([{$unwind: "$laureates"}, {$group: {_id: "$category", awards: {$sum : 1}}}]) // D

56

Demo

#MongoDB

57 of 64

57

Demo

#MongoDB

58 of 64

58

Demo

#MongoDB

59 of 64

What do the following queries do? Part 2

db.prizes.aggregate([{$unwind: "$laureates"}, {$group: {_id: "$category", awards: {$sum : 1}}}]) // D

db.prizes.aggregate([{$unwind: "$laureates"}, {$group: {_id: {category: "$category", year: "$year"}, awards: {$sum : 1}}}]) // E

db.prizes.aggregate([{$unwind: "$laureates"}, {$group: {_id: {category: "$category", year: "$year"}, awards: {$sum : 1}}}, {$sort : {awards: -1}}]) // F

db.prizes.aggregate([{$unwind: "$laureates"}, {$group: {_id: {category: "$category", year: "$year"}, awards: {$sum : 1}}}, {$group: {_id:"$_id.category", avgawards: {$avg : "$awards"}}}]) // G

59

Demo

#MongoDB

60 of 64

60

Demo

#MongoDB

61 of 64

61

Demo

#MongoDB

62 of 64

[Extra] Mongo CLI

Aggregation Queries

Unwind

Lookup

Demo: Multi-Attribute Grouping

Update Queries

MongoDB Summary

[Extra] Demo: Aggregation Queries

[Extra] Mongo CLI

62

Lecture 20, Data 101 Fall 2023

63 of 64

[Reference] MQL Documentation

MQL command-line:

​

PyMongo: https://pymongo.readthedocs.io/en/stable/

​

​

Note: MongoDB Atlas is the integrated MongoDB database suite on the cloud (AWS, Azure, GCP). We are using local mongo.

63

We’ll start with the Python package.

#MongoDB

64 of 64

mongosh # opens mongo shell

Inside mongo shell:

show dbs # client.list_database_names()

use nobel_prizes # db = client.nobel_prizes

show collections # db.list_collection_names()

// note no key quotes:

db.prizes.findOne({category: “chemistry”})

db.prizes.findOne({category: “chemistry”, year: “2020”})

db.prizes.findOne({category: “chemistry”, year: 2020})

db.prizes.findOne({$or: [{category: “chemistry”}, {year: 2020}])

​

(db.prizes.find({category: "peace"},

{_id: 0, category: 1, year: 1,

"laureates.firstname": 1,

"laureates.surname": 1})

.sort({year: 1, category: -1})

.limit(2))

64

Note quotes still used for dot notation

Demo

#MongoDB