Document databases
In short: A document database such as MongoDB stores each record as a document with nested fields and arrays, without one schema for all records.
Problem
Products that are not alike
In the products table every product has the same columns: name, category, price, stock. The shop wants to show more. The weight and the allergens for the bread, the origin and the roast for the coffee, the volume for the dish soap. The table gives two bad choices. The first is dozens of columns that are NULL for most products. The second is a separate "property, value" table, where everything is text and every read is one more JOIN.
An order has a similar problem. To show it to the customer, you put it together from four tables:
The site always shows the order whole: the customer, the lines, the prices. And the database puts it together again every time.
The idea
The document
A document database keeps every record as a document: an object with fields, like JSON. The documents are in a collection, which is like a table but does not decide the fields. Every document has its own. Unlike a key-value database, a document database sees inside the value and searches by its fields.
The field is a simulator with the MongoDB commands. Its console is called mongosh, and the commands start with db. and the name of the collection:
The bread has weight_g and allergens, the coffee origin and roast, the dish soap volume_ml. A value can be an array (allergens) or another document. _id is the primary key: every document has one and it does not repeat.
More precisely, if you are curious
If you do not give an _id, MongoDB adds an ObjectId('…') itself: 12 bytes that hold the time of creation and a random part. The simulator hands them out in order (ObjectId('6720a0000000000000000001'), then …0002), so that the output is the same every time. Real ones do not go like that. Without sort() the simulator returns the documents in the order they went in. MongoDB promises no order at all without sort(). The output is as in mongosh 2: text is in single quotes, and long documents are split one field per line.
Try it
Searching the documents
find(filter, projection) searches. The filter is a document with the fields that have to match. { price: { $lt: 3 } } means "price under 3": $lt is "less than", $lte "less than or equal", $gt and $gte "greater", $ne "not equal", $in "one of the listed". $exists asks whether the field is there at all. The projection says which fields to return: 1 means "yes", and _id: 0 drops _id, which otherwise always comes. .sort({ price: 1 }) sorts in ascending order (-1 is descending), and countDocuments counts:
The filter { allergens: 'milk' } finds the documents whose array contains 'milk'. Careful: { allergens: ['milk'] } looks for exactly that array, with one element and nothing else.
Show the names of the products that contain gluten, without _id.
Solution
db.products.find({ allergens: 'gluten' }, { name: 1, _id: 0 })
db.products.find({ allergens: 'gluten' }, { name: 1, _id: 0 }) finds the bread and the roll. With ['gluten'] the roll drops out, because its array is ['gluten', 'milk']. And with allergen instead of allergens nothing comes out, and the database does not say why.
See it
A typo in the field name
The freedom has a price. A table knows the names of its columns: an UPDATE of a column prise that is not there gives an error. A collection knows no names. The coffee gets cheaper, 12.90 €, but there is a typo in the name of the field:
The database answers modifiedCount: 1 and gives no error. It has added a new field prise, and price is still 14.9. So the coffee does not come out among the products under 13 €. A mistake like this shows only when someone notices the old price on the site.
That is why the program that writes to a document database guards the shape itself: one class or one schema in the code for every kind of document. MongoDB can also check the documents on write against rules for the collection.
More precisely, if you are curious
The rules are set with db.createCollection('products', { validator: { $jsonSchema: … } }) or later with the collMod command. They say which fields are required and of what type. The simulator has no such check.
The idea
Embedding or referencing
The order from the start can be one document, with the customer and the lines inside it. This is called embedding:
findOne returns it whole in one read, with no JOIN. You reach a field in the embedded document with a dot: 'customer.city'. The path is in quotes because it has a dot. The same works through an array. In this field the orders collection already has orders 53-60 of the shop, and 'items.product_id': 1 finds the ones where some line is for the white bread:
The name and the price in the lines are copies, and that is on purpose: the order keeps the price from the day of the purchase, like unit_price in order_items. The name of the customer is a copy too. If Maria changes her name, the old orders keep the old one. For an order that is right, but the profile of the customer lives in its own collection, and the order only carries customer.id.
You embed when the part belongs to the document, is read with it and is small: an order has a few lines, not thousands. You use a reference when something is shared by many documents or grows without end. The reviews of the coffee are like that: there are many, they are written at any time and each has its own author. They are in their own collection, and product_id points to the product, like a foreign key:
Embedding is denormalisation: faster reads in exchange for copies that can drift apart.
More precisely, if you are curious
A document in MongoDB is at most 16 MB, so an array that grows without end sooner or later does not fit in the document. References are joined in aggregate with the $lookup stage (with from, localField, foreignField and as), which does something like a LEFT JOIN. The simulator only knows it in that form. If you need $lookup in every query, your data is probably relational.
Try it
Changes inside the document
updateOne(filter, change) changes the first document it finds, and updateMany all of them. The change is an operator: $set puts a value (and creates the field if it is not there), $inc adds to a number, $push adds to an array, $unset removes a field. This field has orders 53-60 again. Order 60 is delivered, butter is added to it, and the white bread becomes three:
'items.$.qty' means "the qty of the line the filter found". The filter 'items.product_id': 1 finds the line of the white bread, and $ is its place in the array. The document changes whole at once: nobody can read the order half changed.
Without an operator mongosh refuses: updateOne({ _id: 60 }, { status: 'delivered' }) gives the error Update document requires atomic operators. And $set on the whole items array replaces it, and the old lines are gone.
Viktoria (order 58) wants one more wholegrain bread (product 2) and two yoghurts: { product_id: 6, name: 'Yoghurt', qty: 2, price: 1.1 }.
Solution
db.orders.updateOne({ _id: 58, 'items.product_id': 2 }, { $inc: { 'items.$.qty': 1 } })
db.orders.updateOne({ _id: 58 }, { $push: { items: { product_id: 6, name: 'Yoghurt', qty: 2, price: 1.1 } } })
Two changes. First $inc on 'items.$.qty' with the filter 'items.product_id': 2, then $push of the new line. With 'items.0.qty' the first line goes up, and that is the white bread, not the wholegrain one. And $set on items replaces the whole array with one line, and the breads are gone.
The idea
Aggregation
Questions of "how much" and "how much per" (revenue per city, bestselling products) are written with aggregate: an array of stages the documents go through one after the other. This is a pipeline:
$matchfilters, like WHERE;$unwindsplits the order into one document for each line initems;$groupcollects the documents into groups by_id, like GROUP BY, with$sum,$avg,$minand$max;$projectkeeps or computes fields, and$sortand$limitorder and cut.
The revenue per city without the cancelled orders, and then the number of orders:
{ $sum: 1 } counts the documents in the group, like COUNT(*). Without $unwind the groups would add up orders, not lines. $round rounds to the cent: otherwise a sum of fractional numbers comes out as 22.799999999999997 instead of 22.80 €, as with any floating-point numbers.
More precisely, if you are curious
MongoDB does not promise an order of the groups after $group, so a $sort comes after it, and with equal values a second key too (_id: 1). The simulator gives the groups in the order they appeared. It adds fractional numbers a little differently from MongoDB and sometimes differs in the last digit. After $round both give the same.
Exercise
Shipments, allergens and bestsellers
The fields of the tasks have orders 53-60 and the products from the start of the lesson.
1. The courier comes for the paid orders.
Change the status of all orders with status: 'paid' to 'shipped'.
Solution
db.orders.updateMany({ status: 'paid' }, { $set: { status: 'shipped' } })
db.orders.updateMany({ status: 'paid' }, { $set: { status: 'shipped' } }). Orders 55 and 59 are paid. updateOne changes only the first one it finds (55), and 59 stays paid.
2. It turns out that the butter roll also has eggs in it.
Add 'eggs' to the allergens of the butter roll (product 3).
Solution
db.products.updateOne({ _id: 3 }, { $push: { allergens: 'eggs' } })
db.products.updateOne({ _id: 3 }, { $push: { allergens: 'eggs' } }) adds to the end of the array. $set with ['eggs'] replaces the array, and the gluten and the milk are gone. And alergens with one "l" creates a new field, just like prise.
3. The warehouse wants to know what sells the most.
Which products sold 5 or more pieces (without the cancelled orders)? Show them from the bestselling down, and with equal pieces by name.
Solution
db.orders.aggregate([
{ $match: { status: { $ne: 'cancelled' } } },
{ $unwind: '$items' },
{ $group: { _id: '$items.name', sold: { $sum: '$items.qty' } } },
{ $match: { sold: { $gte: 5 } } },
{ $sort: { sold: -1, _id: 1 } },
])
$match removes the cancelled ones, $unwind splits the lines, $group by '$items.name' adds up $sum: '$items.qty', then a second $match on the new field sold (like HAVING) and $sort: { sold: -1, _id: 1 }. Without $unwind the value of '$items.qty' is an array, and $sum in $group skips arrays. Everything comes out 0 and nothing passes $gte: 5.
The idea
When a document database, when not
A document database is good when a record is read and written whole and its shape changes: a catalogue of different products, profiles and settings, the content of a site, events from an app, an order that is shown at once.
It is a bad choice when the data is connected in every direction. The shop asks "which customer bought what", "which products are bought together", "revenue per category and month". Each of these questions goes through customers, orders and products at once. In SQL these are JOINs over normalised tables, where every fact is written once. Here they are copies that have to be kept the same, or a $lookup in every query.
The same goes for an operation that touches many documents: a payment that lowers the stock of five products and saves the order. A change to one document is all or nothing. Several documents need a transaction, which is more complex and slower than a single write.
More precisely, if you are curious
Document databases are MongoDB (the most widespread, with mongosh and aggregate as in the simulator), Couchbase, Apache CouchDB, Firestore in Google Cloud and Amazon DocumentDB, which accepts the MongoDB commands. MongoDB has transactions over many documents since version 4.0. PostgreSQL has the jsonb type: a column with a document in an ordinary table, with indexes and operators for its fields. That is often the good middle way: tables for the connected data and jsonb for the different properties of the products.
Test