Talking to MongoDB: CRUD, filters, projections and nested documents

Four verbs in a JavaScript shell

Every command of this lesson is typed into the shell that the lecture lists among MongoDB's processes: "Mongo – an interactive shell ( a client)", a "Fully functional JavaScript environment for use with a MongoDB" (p. 321). The other two processes of that slide, mongod and mongos, belong to lesson 16; today's shell is mongosh, typed the same way.

So a command is a JavaScript method call — db, the collection, the method — and its arguments are JavaScript objects. A key may go without quotes ({amenities: 1}, p. 1492) unless it contains a dot, and a missing brace is a syntax error before MongoDB sees anything.

CRUD is "Create – Read – Update – Delete" (p. 313). use smbud selects a database, which is created with its first document; a collection, too, appears with its first insert, and db.createCollection('people_collection') creates it empty. The session inserts with insertOne, or with insertMany and "a list of comma-separated documents" (p. 1421):

// adds two documents at once; MongoDB gives each an _id
db.people_collection.insertMany([
   {"name": {"first": "Mike", "last": "Ruby"}, "age": 63},
   {"name": {"first": "Anna", "last": "Verdi"}, "age": 29}
])

find(filter) returns every match, findOne(filter) "the first document that satisfies one or more conditions", countDocuments(filter) their number (p. 1424; lesson 13 introduced them). The lecture's find().pretty() (p. 314) only formats the output.

updateOne "collects the documents that satisfy one or more conditions defined in a filter and updates the first one found"; updateMany updates them all (p. 1425):

// sets Mike's age to 15; his name stays as it is
db.people_collection.updateOne(
   {"age": 63},
   {"$set": {"age": 15}}
)

The second argument must hold an update operator. $set rewrites the fields it names, adds missing ones and leaves the rest alone. Without an operator today's updateOne refuses to run ("Update document requires atomic operators"); to swap the whole document you call replaceOne(filter, newDocument), as the lecture's save did (p. 319).

deleteOne "deletes the first one found", deleteMany "all the collected documents" (p. 1426). The lecture adds db.people_collection.drop(), which removes a collection with its indexes, db.dropDatabase() and show dbs (p. 320).

// deletes Mike, whose age is now 15
db.people_collection.deleteOne(
   {"age": 15}
)

The lecture prints the shell's older method names, the session the current ones — write those at the exam:

The new names say what the old ones hid: the old update changed the first match, the old remove every match.

Every read, update and delete starts from a filter; "One" acts on the first match, "Many" on all, and an update needs an operator such as $set.

A filter is a document

The lecture's first query with a condition (p. 314):

// the female students aged 20 or less
db.students.find({ "gender": "female", "age": {$lte:20} }).pretty()

Read it aloud. A field with a value is an equality; a field with a document applies the operator inside it, here $lte, "less than or equal"; all the fields must hold — an implicit AND, SQL's WHERE gender = 'female' AND age <= 20. "It’s pattern matching again!", the slide adds: you describe the documents you want, as Cypher's MATCH does (lesson 11).

The next slide maps SQL to MongoDB (p. 315). Two of its lines are wrong; the right-hand column gives the form to write.

The session sorts the operators into five families (p. 1430), with an example of each on the slides that follow:

A comparison sits inside a field's value, {"age": {"$gt": 19}}; a logical operator sits at the top and takes an array of complete filters, each in its own braces. The nested example of p. 1432, braces balanced:

// aged 20 to 31, or not 40, 41, 42, 44: returns Mark (37)
db.people_collection.find({
   "$or": [
      {"$and": [
         {"age": {"$gt": 19}},
         {"age": {"$lte": 31}}
      ]},
      {"age": {"$nin": [40, 41, 42, 44]}}
   ]
})

Two conditions on one field may also share a document, {"age": {"$gt": 19, "$lte": 31}}, and $not goes inside a field: {"age": {"$not": {"$gt": 30}}}. One definition on p. 1431 is wrong:

$exists asks whether a field is there at all — the check the exam of 21 January 2025 demands for an optional relationship: "be sure to check that at least one book inspired the movie (-0.5 PT otherwise)". $type asks for the type, {"birth_date": {"$type": "date"}} (p. 1433). $regex finds a pattern anywhere inside a string, case-sensitive: "[A-Z][a-z]+" matches "Mark" (p. 1434), and {"$regex": "Pier"} answers "includes the word “Pier”" (exam of 24 June 2024). $text needs a text index, $where runs JavaScript on each document (p. 1434).

A comparison goes inside a field; a logical operator takes an array of whole filters.

Masks, order and the top N

The second argument of find is a projection: "lists of key-value pairs made by the field name and a boolean value representing whether the field will be returned (1) or not (0). They can be seen as masks" (p. 1427). An inclusion mask keeps the listed fields — plus _id, unless you write "_id": 0; an exclusion mask drops them. The two kinds do not mix ("Cannot do exclusion on field address in inclusion projection").

// only the age of every person, without the _id
db.people_collection.find({}, {"age": 1, "_id": 0})

A dotted path keeps the nested shape: {"host.host_response_rate": 1}, the mask of Ex. 5 (p. 1496), returns {"_id": "10006546", "host": {"host_response_rate": 100}} on the listing of section 4. The slide also computes a "New (Temporary) Field", year — with one character missing:

The next slide fixes the order (p. 1428): find "can only perform filters and projections in that exact order". So find({"year": 1986}, {"year": {"$year": "$birth_date"}}) returns nothing — year does not exist when the filter runs. Filtering on a computed value needs the aggregation pipeline.

Sort and limit (p. 1429): sort "accepts a list of fields and their ordering, i.e. descending (-1) and ascending (1). The earlier a field is referenced, the more relevant it is for the ordering" — sort({"name.first": 1, "name.last": 1}) is ORDER BY first, last. limit(n) keeps the first n, so a top N is a descending sort and a limit — Ex. 4 (p. 1495):

// the 10 housings with the most bedrooms; shows bedrooms (and _id)
db.air_bnb_collection.find({},
  { "bedrooms": 1 }
).sort(
  { "bedrooms": -1 }
).limit(10)

On a find the two calls are settings of one query: .limit(2).sort(…) still sorts first. In the pipeline of lesson 15 the stages run in the order written, and $limit before $sort keeps arbitrary documents. Ties come back in no fixed order; add a second sort key if it matters.

find filters first and applies the mask after; sort decides the order, limit cuts.

Types are data: the AirBnB listing

The second half of the session queries the AirBnB sample dataset that MongoDB offers with its free Atlas clusters, imported as air_bnb_collection; pp. 1472–1474 show its first document, the "Ribeira Charming Duplex" in Porto. One listing is one tree: plain values, sub-documents (host, address, availability, review_scores, images), an array of values (amenities) and an array of sub-documents (reviews) — what the session calls a sub-collection. Ex. 1 (p. 1492) inspects them one by one:

// the first housing, showing only its amenities (and _id)
db.air_bnb_collection.findOne({ }, {amenities: 1})

On the slide, minimum_nights: "2" is printed in quotes and green, number_of_reviews: 51 without quotes and blue (p. 1472): a string and a number, as MongoDB's documentation of the dataset confirms. Hence the quotes of Ex. 2 (p. 1493), whose footnote returns the field "just to check that the answer is correct":

// housings whose minimum nights is the string "2"
db.air_bnb_collection.find(
  { "minimum_nights": { "$eq": "2" } },
  { "minimum_nights": 1 }
)

MongoDB compares a filter's value only with values of the same kind — strings with strings, numbers with numbers, dates with dates. "2" and 2 are different values, and {"$gt": "10"} against stored numbers returns nothing, without an error; within numbers the storage type does not matter (80 finds 80.00). At the exam the types are those you declared in your documental representation — the papers ask for "all attributes and their types" — and the queries must use them: counts and amounts as numbers, names and codes as strings, dates as ISODate("2020-03-17").

Ex. 3 asks for a number, and the session's solution returns something else:

Ex. 5 (p. 1496) reaches into a sub-document:

// housings whose host response rate is over 90; shows the rate
db.air_bnb_collection.find(
  { "host.host_response_rate": { "$gt": 90 } },
  { "host.host_response_rate": 1 }
)

The dotted key needs quotes, since host.host_response_rate is not a valid JavaScript key. Otherwise a sub-document behaves like plain fields — such conditions "are not subject to any particular behaviour change" (p. 1466) — and $gt is strict, so a host at exactly 90 stays out. A whole sub-document in a filter, {"address": {"city": "Milan"}}, must match exactly, every field in the same order: it finds nothing.

Arrays of values: contains, all of, any of

Ex. 6 and Ex. 7 (pp. 1497–1498) differ by one operator, $and against $or:

// housings that include a TV and an Iron; shows name and amenities
db.air_bnb_collection.find(
  {
    "$and": [
      { "amenities": "TV" },
      { "amenities": "Iron" }
    ],
  },
  { "name": 1, "amenities": 1 }
)

On an array, {"amenities": "TV"} means "contains "TV"". Two such conditions need $and, because a JavaScript object cannot hold the key amenities twice (the trailing comma, by the way, is legal in the shell). The array family has shorter forms; on the widget's listings they return:

The highlighted row is the trap: brackets ask for the whole array, not its contents. And $size takes a number only — {"$size": {"$gt": 1}} is refused.

A value in a filter has a type — "2" is not 2 — and one value on an array of values means "contains".

Arrays of sub-documents: the same element or any element

Back to Mark (p. 1419): mobile_phone_numbers is a sub-collection. One dotted condition tests every element — "queries evaluating a single condition on the fields of the documents of an array (sub-collection) will return the main document if at least one of the documents in the array satisfies the condition" (p. 1436):

// people with at least one Wind phone: returns Mark
db.people_collection.find(
   {"mobile_phone_numbers.service_provider": "Wind"}
)

Several conditions are "assessed individually on the array’s documents": "It doesn’t matter if one item matches all the conditions or if different items match different conditions" (p. 1437). That is the warm-up:

// returns Mark: Wind is found in one element, YYY in the other
db.people_collection.find(
   {"mobile_phone_numbers.service_provider": "Wind",
    "mobile_phone_numbers.phone_number": "+39 YYY 9874 123"}
)

To tie the conditions to one element, $elemMatch "matches documents containing an array field with at least one document that satisfies all the specified query criteria" (p. 1438):

// returns Mark: TIM and YYY sit in the same element
db.people_collection.find({
   "mobile_phone_numbers": {
      "$elemMatch": {
         "service_provider": "TIM",
         "phone_number": "+39 YYY 9874 123"
      }
   }
})

With Wind in place of TIM the same filter returns nothing.

Step by step, on Mark:

1. Condition A, provider "Wind": the path runs into an array, so MongoDB tries every element. Element 0 says Wind — A holds.

2. Condition B, number "+39 YYY 9874 123": element 0 no, element 1 yes — B holds, on another element.

3. Both conditions hold, so Mark is returned: nothing asked them to look at the same element (p. 1437).

4. $elemMatch swaps the loops: one element at a time, all the conditions on it. Element 0: Wind yes, YYY no; element 1: Wind no, YYY yes. No element passes both — Mark is not returned.

5. With TIM and YYY (p. 1438) element 1 passes both, and both forms return Mark.

In logic the two filters differ only in the order of two quantifiers:

The second line implies the first, but not the reverse: $elemMatch never returns more documents, and with one condition the two coincide. The widget replays the session's pair on reviews: Ex. 14 asks for "at least one review with listing id 10006546 and reviewer id 11263097" (p. 1505, solved with $elemMatch), Ex. 15 for "at least one review with listing id 10006546 and one with reviewer name Bridget" (p. 1506, dotted paths).

So read the question first. "At least one X with A and B" — one element, both properties — is $elemMatch; "one X with A and one with B" is two dotted paths; a single condition is the same either way. The warm-up's chef question is the first kind, and it is the first exam question below.

Several dotted conditions on an array may be satisfied by different elements; $elemMatch demands one element that satisfies them all.

Indexes: fast reads, paid for by writes

Without an index, find reads every document and tests the filter on each. An index is a second structure kept sorted — "B+ tree indexes" (p. 322); for the session, "data structures that store a small portion of the collection's data set in an easy to traverse form, ordered by the value of the field" (p. 1422). A B+ tree reaches a value in a few steps and then reads its neighbours in order, so one index serves equality, ranges and sorting.

Page 322 lists the rest. "An index is automatically created on the _id field (the primary key)", which is what keeps every _id unique; "Users can create other indexes to improve query performance or to enforce Unique values for a particular field"; they are single-field or compound, and "Like SQL order of the fields in a compound index matters"; an indexed array gets "separate index entries for every element of the array" — a multikey index, in the current manual. The session's compound index (p. 1422):

// a compound index: last name descending, then first name, then age
db.people_collection.createIndex(
   {"name.last": -1, "name.first": 1, "age": 1}
)

1 and -1 mean ascending and descending, as in sort. Why the order of the fields matters shows on the leaves of the tree:

This is the prefix rule: the index serves name.last, name.last with name.first, or all three — a prefix of its fields — but not name.first or age alone. A sort on name.last can use it either way round, since the tree reads backwards too. A composite index in SQL behaves the same: hence "Like SQL".

Page 323 adds: "Sparse property of an index ensures that the index only contain entries for documents that have the indexed field", and "If an index is both unique and sparse – then the system will reject records that have a duplicate key value but allow records that do not have the indexed field defined". Why: a unique index that is not sparse files a missing field as null, so a second document without it is a duplicate null and is refused. The typical case is an optional e-mail, unique when present:

// e-mail optional, but unique when present
db.users.createIndex({"email": 1}, {"unique": true, "sparse": true})

The price is printed under the session's index, in green and red: "Faster Queries and Improved Read Performance but Slower Writes and Increased Storage" (p. 1422). Every insert and delete must also update every index, an update every index on a field it changes, and every index takes disk space — an indexed array of twenty values adds twenty entries per document. Index what your queries filter and sort on, not everything.

An index trades write speed and storage for read speed, and a compound index serves only the prefixes of its fields.