# MongoDB for SQL developers: document databases explained with the planets database

![Version badges: MongoDB 9.0, mongosh 2.14, MySQL 9.7 LTS, tested November 2026](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/88efcc88-e493-451a-9d5c-848ce9dd32e1.png)

If you've learnt databases the relational way, with tables, primary and foreign keys, normalisation, joins and transactions, MongoDB can feel like someone has taken the rules away. There's no `CREATE TABLE`, a misspelt field name is accepted without complaint, and the advice is to *copy* data rather than normalise it. This tutorial explains what a document database gives you in return, where it's better than a relational database, where it's worse, and the habits from SQL that will trip you up.

We'll take one small, familiar database, the **planets and moons** from my post [Exploring the Cosmos](https://pedbad.hashnode.dev/exploring-the-cosmos-building-and-querying-a-planetary-database-with-mysql), and build it twice: once in **MySQL** and once in **MongoDB**. We'll ask both the same questions and compare the answers, the queries and the design decisions along the way, then finish with what each model is good at.

This is part 2 of two posts on distributed and NoSQL databases. Part 1, [Distributed databases: partitioning, replication and CAP](https://pedbad.hashnode.dev/distributed-databases-partitioning-replication-and-cap), explains why spreading a database over many machines is hard. You don't need to read it first, but section 1 picks up where it left off.

**Tested with** MongoDB Community Server 9.0.8, mongosh 2.14.2 and MySQL 9.7.4 LTS on Linux, 19 November 2026. Every command and every output below comes from that run.

## Contents

1. Why NoSQL?
2. Four families of NoSQL database
3. Documents and JSON
4. Setting up MongoDB
5. From SQL to MongoDB: a phrasebook
6. The planets database in MySQL
7. The same data as documents: embed or reference?
8. The two questions in MongoDB
9. Reading and writing documents
10. Surprises for SQL developers
11. Indexes and `explain()`
12. Schema validation: rules when you want them
13. Transactions: ACID in MongoDB
14. MapReduce then and now (and Hadoop)
15. Scaling out: replica sets and sharding
16. MySQL can store documents too
17. Strengths, weaknesses and choosing
18. Check your understanding
19. Further reading and sources

## 1. Why NoSQL?

![A spectrum from relational to key/value databases: the further right, the less structure the database enforces and the easier the data is to spread over many machines; document databases sit in the middle](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/9969227e-f185-4ee6-93cd-98b6216cb834.png)

A relational database is built on one generic structure, the table, and on strong guarantees: a schema, keys, foreign keys and transactions. Those guarantees are what make it so dependable, and they're also what make it hard to spread over many machines. In part 1 we saw why: a join or a foreign key check may need rows that live on another server, and a transaction that touches two servers needs a coordinator and two-phase commit.

One response is to **simplify the data**. At the far end is a **key/value store**: just a key and a value that the database treats as an opaque blob. Every pair is independent of every other, so you can split them across as many machines as you like.

**Document databases** sit in the middle. Like a key/value store, each document stands on its own and is fetched by its key. Unlike a key/value store, the database understands what's inside: a document can have nested objects and lists, and you can query, index and update any field in it. You give up joins as the default way of working and some of the guarantees, and in exchange the data is easier to distribute and often closer to the objects in your program.

"NoSQL" doesn't mean "no SQL is allowed". The usual reading today is "not only SQL": a family of databases that don't use the relational model as their main way of storing data.

## 2. Four families of NoSQL database

| Family | Stores | Good for | Example |
|---|---|---|---|
| **Key/value** | a value looked up by its key | caches, sessions, settings, anything fetched by an id | Redis, Amazon DynamoDB |
| **Document** | self-contained documents (JSON-like) in collections | records with nested or varied structure, read as a whole | MongoDB, CouchDB |
| **Wide-column** | rows with many, sparse, grouped columns, spread by a partition key | huge write-heavy data sets, such as sensor readings or event logs | Apache Cassandra, HBase |
| **Graph** | nodes and the relationships between them | data that *is* relationships: networks, recommendations, routes | Neo4j |

The rest of this post is about document databases, using MongoDB. Many of the ideas carry over to the others.

## 3. Documents and JSON

A document looks like a JSON object: names and values, where a value can be a number, a string, `true`/`false`, `null`, a list (array) or another object. Here is a moon:

```json
{ "name": "Titan", "liquidWater": "Yes" }
```

JSON (JavaScript Object Notation) is a text format for exchanging data, defined in RFC 8259 and ECMA-404. It looks like a JavaScript object literal, but it's stricter, and the differences matter as soon as you write JSON by hand:

- names (keys) **must** be in double quotes: `"name"`, not `name` or `'name'`;
- strings must be in double quotes too;
- **no trailing comma** after the last item;
- **no comments**;
- no variables, functions or references: one JSON document can't point at another by name.

You can check this in mongosh, MongoDB's shell (we'll install it in section 4), because it's a JavaScript environment with `JSON.parse()` built in. The first line is valid JSON; each of the others breaks one rule:

```javascript
JSON.parse('{ "name": "Titan", "liquidWater": "Yes" }')
JSON.parse('{ name: "Titan" }')
JSON.parse("{ 'name': 'Titan' }")
JSON.parse('{ "name": "Titan", }')
JSON.parse('{ "name": "Titan" /* largest moon of Saturn */ }')
```

Output:

```text
{ name: 'Titan', liquidWater: 'Yes' }
Uncaught:
SyntaxError: Expected property name or '}' in JSON at position 2 (line 1 column 3)
Uncaught:
SyntaxError: Expected property name or '}' in JSON at position 2 (line 1 column 3)
Uncaught:
SyntaxError: Expected double-quoted property name in JSON at position 19 (line 1 column 20)
Uncaught:
SyntaxError: Expected ',' or '}' after property value in JSON at position 18 (line 1 column 19)
```

The unquoted name, the single quotes, the trailing comma and the comment are all rejected. (In mongosh itself you may type keys without quotes, because mongosh commands are JavaScript, not JSON. That's why most MongoDB examples, including the ones below, don't quote the keys.)

Another quick check, if you have Python: `python3 -m json.tool planets.json` prints the file nicely or tells you where it breaks.

MongoDB doesn't actually store text. It stores **BSON** (binary JSON), which adds types JSON doesn't have, such as dates, 64-bit integers, decimals and `ObjectId`, the 12-byte identifier MongoDB creates when you don't give a document an `_id` of your own.

## 4. Setting up MongoDB

You need two programs: the server, `mongod`, and the shell, `mongosh`.

- **macOS:** `brew tap mongodb/brew` then `brew install mongodb-community` (it installs mongosh as well). On 19 November 2026 the tap installs MongoDB 9.0.8.
- **Linux:** add MongoDB's package repository and install `mongodb-org`, following the [installation guide](https://www.mongodb.com/docs/manual/administration/install-on-linux/) for your distribution.
- **Windows:** the MSI installer from the [MongoDB Community Server download page](https://www.mongodb.com/try/download/community); install [mongosh](https://www.mongodb.com/try/download/shell) separately.

I used the official Linux download (the `.tgz` archive) for MongoDB 9.0.8 and mongosh 2.14.2 from npm. MongoDB Community Server is free to use; its licence is MongoDB's Server Side Public License (SSPL), which isn't an OSI-approved open-source licence. That only matters if you plan to offer MongoDB itself as a service.

For MySQL I used MySQL 9.7.4 LTS; anything from 8.0 on runs the SQL in this post. My post [Install MySQL on OSX Using Homebrew](https://pedbad.hashnode.dev/install-mysql-on-osx-using-homebrew) covers the Mac.

### Start a one-member replica set

Section 13 uses multi-document transactions, and MongoDB only allows those on a **replica set** (a group of servers that keep copies of the same data; more in section 15). A replica set with one member is enough for learning, so we'll start the server that way from the beginning. In one terminal:

```bash
mkdir -p mongo-lab/data
mongod --dbpath mongo-lab/data --replSet rs0 --bind_ip 127.0.0.1
```

Leave it running. If you installed with Homebrew and started it with `brew services start mongodb-community`, stop that first (`brew services stop mongodb-community`): it runs a standalone server on the same port.

In a second terminal, open the shell with `mongosh` and turn the server into a replica set, once:

```javascript
rs.initiate()
```

Output (your timestamps will differ):

```text
{
  info2: 'no configuration specified. Using a default configuration for the set',
  me: '127.0.0.1:27017',
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1795057422, i: 1 }),
    signature: {
      hash: Binary.createFromBase64('AAAAAAAAAAAAAAAAAAAAAAAAAAA=', 0),
      keyId: Long('0')
    }
  },
  operationTime: Timestamp({ t: 1795057422, i: 1 })
}
```

The prompt changes to `rs0 [direct: primary] test>`: you're connected to the primary of replica set `rs0`, using a database called `test`. Next time, start `mongod` with the same command and skip `rs.initiate()`.

> **Local learning only.** This server has no users or passwords and only listens on your own machine (`127.0.0.1`). Never run a real server like this.

## 5. From SQL to MongoDB: a phrasebook

| SQL | MongoDB | Note |
|---|---|---|
| database | database | `use solar_embedded` switches to it (and creates it on first write) |
| table | collection | created automatically the first time you insert |
| row | document | can contain nested objects and arrays |
| column | field | documents in one collection may have different fields |
| primary key | `_id` | every document has one; MongoDB makes an `ObjectId` if you don't |
| foreign key | a reference (a field holding another document's `_id`) | **not checked** by the database |
| `JOIN` | embedding, or `$lookup` in an aggregation | |
| `WHERE` | the filter document in `find()` | `{ diameter: { $gt: 12000 } }` |
| `SELECT col1, col2` | the projection in `find()` | `{ diameter: 1 }` |
| `GROUP BY` and aggregates | `$group` in an aggregation pipeline | |
| `UNIQUE` | a unique index | |
| `CREATE TABLE` | nothing, or schema validation if you want rules | section 12 |
| transaction | transaction | needs a replica set; section 13 |

## 6. The planets database in MySQL

Here's the relational version. It's the *Exploring the Cosmos* database with two additions: each planet now has its diameter, and `Moons.PlanetName` is a real foreign key. Planet figures are rounded from NASA's [Planetary Fact Sheet](https://nssdc.gsfc.nasa.gov/planetary/factsheet/): diameter in km, length of day in hours, length of year in Earth days. The moons are a selection, not all of them (Saturn alone has 274 according to the fact sheet), and `HasLiquidWater` records whether a moon has liquid water: **Yes** (an ocean is confirmed or very likely), **Suspected**, **No** or **Unknown**, as explained in that post.

![ER diagram: a Planet has PlanetName (primary key), Diameter, DayLength and YearLength; a Moon has PlanetName and MoonName (together the primary key) and HasLiquidWater; each moon orbits exactly one planet, and a planet has zero or more moons](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/e8a4376d-3781-48c8-9f01-1ef5638e8c52.png)

Save this as `planets.sql` and run it with `mysql -u root -p < planets.sql`:

```sql
-- The planets database in MySQL
-- Planet figures: NASA Planetary Fact Sheet (diameter in km,
-- length of day in hours, orbital period in Earth days), rounded.
DROP DATABASE IF EXISTS Astronomy;
CREATE DATABASE Astronomy;
USE Astronomy;

CREATE TABLE Planets (
    PlanetName VARCHAR(25) PRIMARY KEY,
    Diameter   INT NOT NULL,
    DayLength  INT NOT NULL,
    YearLength INT NOT NULL
);

CREATE TABLE Moons (
    PlanetName     VARCHAR(25)  NOT NULL,
    MoonName       VARCHAR(100) NOT NULL,
    HasLiquidWater ENUM('Yes', 'No', 'Unknown', 'Suspected') NOT NULL,
    PRIMARY KEY (PlanetName, MoonName),
    FOREIGN KEY (PlanetName) REFERENCES Planets (PlanetName)
);

INSERT INTO Planets (PlanetName, Diameter, DayLength, YearLength) VALUES
    ('Mercury',   4879, 4223,    88),
    ('Venus',    12104, 2802,   225),
    ('Earth',    12756,   24,   365),
    ('Mars',      6792,   25,   687),
    ('Jupiter', 142984,   10,  4331),
    ('Saturn',  120536,   11, 10747),
    ('Uranus',   51118,   17, 30589),
    ('Neptune',  49528,   16, 59800);

INSERT INTO Moons (PlanetName, MoonName, HasLiquidWater) VALUES
    ('Earth',   'Moon',      'No'),
    ('Mars',    'Phobos',    'No'),
    ('Mars',    'Deimos',    'No'),
    ('Jupiter', 'Io',        'No'),
    ('Jupiter', 'Europa',    'Yes'),
    ('Jupiter', 'Ganymede',  'Yes'),
    ('Jupiter', 'Callisto',  'Suspected'),
    ('Saturn',  'Titan',     'Yes'),
    ('Saturn',  'Enceladus', 'Yes'),
    ('Saturn',  'Rhea',      'Unknown'),
    ('Saturn',  'Iapetus',   'Unknown'),
    ('Saturn',  'Dione',     'Suspected'),
    ('Uranus',  'Titania',   'Suspected'),
    ('Uranus',  'Oberon',    'Suspected'),
    ('Uranus',  'Umbriel',   'Suspected'),
    ('Uranus',  'Ariel',     'Suspected'),
    ('Neptune', 'Triton',    'Suspected'),
    ('Neptune', 'Nereid',    'Unknown'),
    ('Neptune', 'Proteus',   'Unknown'),
    ('Neptune', 'Larissa',   'Unknown');
```

Two questions we'll ask in both databases:

1. **Which moons have, or may have, liquid water, and how long is a day on their planet?**
2. **How many moons does each planet have in our data, including planets with none?**

Connect with `mysql -u root -p Astronomy` to try them. In SQL the first is a join and the second is a `LEFT JOIN` with `GROUP BY`:

```sql
SELECT m.MoonName, m.PlanetName, p.DayLength, m.HasLiquidWater
FROM Moons AS m
JOIN Planets AS p ON p.PlanetName = m.PlanetName
WHERE m.HasLiquidWater IN ('Yes', 'Suspected')
ORDER BY p.DayLength, m.MoonName;
```

```text
+-----------+------------+-----------+----------------+
| MoonName  | PlanetName | DayLength | HasLiquidWater |
+-----------+------------+-----------+----------------+
| Callisto  | Jupiter    |        10 | Suspected      |
| Europa    | Jupiter    |        10 | Yes            |
| Ganymede  | Jupiter    |        10 | Yes            |
| Dione     | Saturn     |        11 | Suspected      |
| Enceladus | Saturn     |        11 | Yes            |
| Titan     | Saturn     |        11 | Yes            |
| Triton    | Neptune    |        16 | Suspected      |
| Ariel     | Uranus     |        17 | Suspected      |
| Oberon    | Uranus     |        17 | Suspected      |
| Titania   | Uranus     |        17 | Suspected      |
| Umbriel   | Uranus     |        17 | Suspected      |
+-----------+------------+-----------+----------------+
11 rows in set
```

```sql
SELECT p.PlanetName, COUNT(m.MoonName) AS MoonCount
FROM Planets AS p
LEFT JOIN Moons AS m ON m.PlanetName = p.PlanetName
GROUP BY p.PlanetName
ORDER BY MoonCount DESC, p.PlanetName;
```

```text
+------------+-----------+
| PlanetName | MoonCount |
+------------+-----------+
| Saturn     |         5 |
| Jupiter    |         4 |
| Neptune    |         4 |
| Uranus     |         4 |
| Mars       |         2 |
| Earth      |         1 |
| Mercury    |         0 |
| Venus      |         0 |
+------------+-----------+
8 rows in set
```

Notice the `LEFT JOIN` and `COUNT(m.MoonName)` in the second query: an inner join, or `COUNT(*)`, would quietly lose or miscount Mercury and Venus. Keep that in mind for section 8.

## 7. The same data as documents: embed or reference?

![The same planet in three shapes: two MySQL tables joined by PlanetName; one embedded document where Saturn holds an array of its moons; and two collections, planets and moons, where each moon document holds a planet field that refers to a planet's _id](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/23758090-85d8-421f-8ec7-d6cd4ce9057b.png)

In a relational design there's usually one right answer: normalise, one table per entity, and join when you query. In a document database the big design decision is **what goes inside what**. For each relationship you either:

- **embed** the related data inside the parent document (Saturn's moons in an array inside Saturn), or
- **reference** it: keep it in its own collection and store the parent's `_id` in it, much like a foreign key.

![Embed or reference? Embed when the related data belongs to one parent, is read with it and stays small (one-to-few). Reference when it's shared between parents (many-to-many), queried on its own, or can grow without limit. The choice decides how many collections you need](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/6efed251-e621-4362-9838-d20d9680cb8b.png)

Rules of thumb:

| Embed when… | Reference when… |
|---|---|
| the child belongs to exactly one parent (a moon orbits one planet) | the child is shared by many parents (many-to-many, such as space missions that visited several planets) |
| you nearly always read them together | you often query the child on its own |
| the list stays small ("one-to-few") | the list can grow without limit (every observation ever made of a planet) |
| you want one atomic update of parent and children | the child is updated often and independently |

A document can't be bigger than 16 MiB, which is another reason not to embed lists that keep growing.

For the planets, both designs are reasonable, so we'll build both and compare. **Embedded: one collection.** Each planet document holds its moons. This file is valid JSON (keys and strings in double quotes, no trailing commas); save it as `planets.json`:

```json
[
  {
    "_id": "Mercury",
    "diameter": 4879,
    "dayLength": 4223,
    "yearLength": 88,
    "moons": []
  },
  {
    "_id": "Venus",
    "diameter": 12104,
    "dayLength": 2802,
    "yearLength": 225,
    "moons": []
  },
  {
    "_id": "Earth",
    "diameter": 12756,
    "dayLength": 24,
    "yearLength": 365,
    "moons": [
      { "name": "Moon", "liquidWater": "No" }
    ]
  },
  {
    "_id": "Mars",
    "diameter": 6792,
    "dayLength": 25,
    "yearLength": 687,
    "moons": [
      { "name": "Phobos", "liquidWater": "No" },
      { "name": "Deimos", "liquidWater": "No" }
    ]
  },
  {
    "_id": "Jupiter",
    "diameter": 142984,
    "dayLength": 10,
    "yearLength": 4331,
    "moons": [
      { "name": "Io", "liquidWater": "No" },
      { "name": "Europa", "liquidWater": "Yes" },
      { "name": "Ganymede", "liquidWater": "Yes" },
      { "name": "Callisto", "liquidWater": "Suspected" }
    ]
  },
  {
    "_id": "Saturn",
    "diameter": 120536,
    "dayLength": 11,
    "yearLength": 10747,
    "moons": [
      { "name": "Titan", "liquidWater": "Yes" },
      { "name": "Enceladus", "liquidWater": "Yes" },
      { "name": "Rhea", "liquidWater": "Unknown" },
      { "name": "Iapetus", "liquidWater": "Unknown" },
      { "name": "Dione", "liquidWater": "Suspected" }
    ]
  },
  {
    "_id": "Uranus",
    "diameter": 51118,
    "dayLength": 17,
    "yearLength": 30589,
    "moons": [
      { "name": "Titania", "liquidWater": "Suspected" },
      { "name": "Oberon", "liquidWater": "Suspected" },
      { "name": "Umbriel", "liquidWater": "Suspected" },
      { "name": "Ariel", "liquidWater": "Suspected" }
    ]
  },
  {
    "_id": "Neptune",
    "diameter": 49528,
    "dayLength": 16,
    "yearLength": 59800,
    "moons": [
      { "name": "Triton", "liquidWater": "Suspected" },
      { "name": "Nereid", "liquidWater": "Unknown" },
      { "name": "Proteus", "liquidWater": "Unknown" },
      { "name": "Larissa", "liquidWater": "Unknown" }
    ]
  }
]
```

I used the planet's name as `_id`, as the SQL table uses `PlanetName` as its primary key. Field names follow MongoDB's usual camelCase style. A planet with no moons gets an empty array rather than no `moons` field, so every document has the same shape; MongoDB wouldn't insist on that, but your queries will be simpler.

Start `mongosh` **in the folder where you saved `planets.json`**, then load it into a new database, `solar_embedded`:

```javascript
use solar_embedded
const fs = require('fs')
const planets = JSON.parse(fs.readFileSync('planets.json', 'utf8'))
db.planets.insertMany(planets)
```

```text
{
  acknowledged: true,
  insertedIds: {
    '0': 'Mercury',
    '1': 'Venus',
    '2': 'Earth',
    '3': 'Mars',
    '4': 'Jupiter',
    '5': 'Saturn',
    '6': 'Uranus',
    '7': 'Neptune'
  }
}
```

```javascript
db.planets.findOne({ _id: "Mars" })
```

```text
{
  _id: 'Mars',
  diameter: 6792,
  dayLength: 25,
  yearLength: 687,
  moons: [
    { name: 'Phobos', liquidWater: 'No' },
    { name: 'Deimos', liquidWater: 'No' }
  ]
}
```

There was no `CREATE DATABASE` and no `CREATE TABLE`: the database and the collection appeared when the first document was written.

**Referenced: two collections.** In `solar_referenced` the planets have no `moons` array, and each moon is its own document with a `planet` field holding its planet's `_id`. The same file can be reshaped as we load it:

```javascript
use solar_referenced
const fs = require('fs')
const planets = JSON.parse(fs.readFileSync('planets.json', 'utf8'))
db.planets.insertMany(planets.map(({ moons, ...planet }) => planet))
db.moons.insertMany(planets.flatMap(p =>
  p.moons.map(m => ({ name: m.name, planet: p._id, liquidWater: m.liquidWater }))
))
```

```javascript
db.moons.findOne({ name: "Phobos" })
```

```text
{
  _id: ObjectId('6afe6711fc6e7b900dc53018'),
  name: 'Phobos',
  planet: 'Mars',
  liquidWater: 'No'
}
```

```javascript
db.planets.countDocuments()
db.moons.countDocuments()
```

```text
8
20
```

The moons didn't get an `_id` from us, so MongoDB made an `ObjectId` for each (yours will differ). This is now very close to the SQL design: `planet` plays the part of the foreign key. The difference, as we'll see in section 10, is that nothing checks it.

How big is a planet with its moons embedded? `bsonsize()` gives a document's size in bytes:

```javascript
use solar_embedded
bsonsize(db.planets.findOne({ _id: "Saturn" }))
```

```text
322
```

322 bytes, a long way from 16 MiB.

## 8. The two questions in MongoDB

Complex queries in MongoDB are written as an **aggregation pipeline**: a list of stages, each taking the documents from the stage before, like an assembly line. It's the counterpart of a `SELECT` with joins, `WHERE`, `GROUP BY` and `ORDER BY`, written in the order the work happens.

![An aggregation pipeline: documents flow through $unwind (one document per moon), $match (like WHERE), $project (like the SELECT list), $group (like GROUP BY) and $sort (like ORDER BY); $lookup brings in documents from another collection, like a LEFT OUTER JOIN](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/5919cf25-d93d-49a3-bc0c-b4f8bae898a4.png)

### Question 1: moons that have, or may have, liquid water

**Embedded.** The moons are inside the planets, so first `$unwind` the array: it turns one planet with five moons into five documents, one per moon, each carrying the planet's fields. Then filter, reshape and sort:

```javascript
use solar_embedded
db.planets.aggregate([
  { $unwind: "$moons" },
  { $match: { "moons.liquidWater": { $in: ["Yes", "Suspected"] } } },
  { $project: {
      _id: 0,
      moon: "$moons.name",
      planet: "$_id",
      dayLength: "$dayLength",
      liquidWater: "$moons.liquidWater"
  } },
  { $sort: { dayLength: 1, moon: 1 } }
])
```

```text
[
  {
    moon: 'Callisto',
    planet: 'Jupiter',
    dayLength: 10,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Europa',
    planet: 'Jupiter',
    dayLength: 10,
    liquidWater: 'Yes'
  },
  {
    moon: 'Ganymede',
    planet: 'Jupiter',
    dayLength: 10,
    liquidWater: 'Yes'
  },
  {
    moon: 'Dione',
    planet: 'Saturn',
    dayLength: 11,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Enceladus',
    planet: 'Saturn',
    dayLength: 11,
    liquidWater: 'Yes'
  },
  { moon: 'Titan', planet: 'Saturn', dayLength: 11, liquidWater: 'Yes' },
  {
    moon: 'Triton',
    planet: 'Neptune',
    dayLength: 16,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Ariel',
    planet: 'Uranus',
    dayLength: 17,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Oberon',
    planet: 'Uranus',
    dayLength: 17,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Titania',
    planet: 'Uranus',
    dayLength: 17,
    liquidWater: 'Suspected'
  },
  {
    moon: 'Umbriel',
    planet: 'Uranus',
    dayLength: 17,
    liquidWater: 'Suspected'
  }
]
```

The same 11 moons, in the same order, as the SQL query, and no join was needed: each moon already sits next to its planet's `dayLength`.

**Referenced.** Here the moons and the day length are in different collections, so we need a join. In MongoDB that's the `$lookup` stage. It adds an array (`planetDoc`) holding the matching planet documents, which `$unwind` flattens:

```javascript
use solar_referenced
db.moons.aggregate([
  { $match: { liquidWater: { $in: ["Yes", "Suspected"] } } },
  { $lookup: {
      from: "planets",
      localField: "planet",
      foreignField: "_id",
      as: "planetDoc"
  } },
  { $unwind: "$planetDoc" },
  { $project: {
      _id: 0,
      moon: "$name",
      planet: "$planet",
      dayLength: "$planetDoc.dayLength",
      liquidWater: "$liquidWater"
  } },
  { $sort: { dayLength: 1, moon: 1 } }
])
```

It returns exactly the same 11 documents as the embedded version. `$lookup` behaves like a left outer join: a moon whose planet didn't exist would get an empty `planetDoc` array, and `$unwind` would then drop it.

### Question 2: how many moons each planet has

**Embedded.** The moons of each planet are already counted in one place: the length of its array.

```javascript
db.planets.aggregate([
  { $project: { moonCount: { $size: "$moons" } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
```

```text
[
  { _id: 'Saturn', moonCount: 5 },
  { _id: 'Jupiter', moonCount: 4 },
  { _id: 'Neptune', moonCount: 4 },
  { _id: 'Uranus', moonCount: 4 },
  { _id: 'Mars', moonCount: 2 },
  { _id: 'Earth', moonCount: 1 },
  { _id: 'Mercury', moonCount: 0 },
  { _id: 'Venus', moonCount: 0 }
]
```

**Referenced.** The obvious translation of `GROUP BY` is `$group` on the moons:

```javascript
db.moons.aggregate([
  { $group: { _id: "$planet", moonCount: { $sum: 1 } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
```

```text
[
  { _id: 'Saturn', moonCount: 5 },
  { _id: 'Jupiter', moonCount: 4 },
  { _id: 'Neptune', moonCount: 4 },
  { _id: 'Uranus', moonCount: 4 },
  { _id: 'Mars', moonCount: 2 },
  { _id: 'Earth', moonCount: 1 }
]
```

Mercury and Venus have gone: they have no moon documents to group, exactly the trap the SQL `LEFT JOIN` avoided. To include them, start from the planets and look their moons up:

```javascript
db.planets.aggregate([
  { $lookup: {
      from: "moons",
      localField: "_id",
      foreignField: "planet",
      as: "moons"
  } },
  { $project: { moonCount: { $size: "$moons" } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
```

```text
[
  { _id: 'Saturn', moonCount: 5 },
  { _id: 'Jupiter', moonCount: 4 },
  { _id: 'Neptune', moonCount: 4 },
  { _id: 'Uranus', moonCount: 4 },
  { _id: 'Mars', moonCount: 2 },
  { _id: 'Earth', moonCount: 1 },
  { _id: 'Mercury', moonCount: 0 },
  { _id: 'Venus', moonCount: 0 }
]
```

Now all eight planets are there, with the same counts as MySQL.

### What changed, and which would I choose?

- **Modelling.** The relational design came first and the queries followed; any question could be asked later. In MongoDB I had to decide up front how the data would be read. The embedded design fits the planets well: each moon belongs to one planet, the lists are short and we usually want a planet with its moons. The referenced design mirrors the tables and needs two collections instead of one.
- **What got simpler.** With moons embedded, both questions needed no join, and question 2 was just the size of an array. A planet with its moons is one read and one document, and updating a planet and its moons together is one atomic operation (section 13).
- **What got harder.** Any question about *moons* on their own (all moons with water, sorted) has to `$unwind` the planets first. If the same moon could belong to several parents, or we stored thousands of measurements per moon, embedding would mean duplicated data or huge documents. In the referenced design, question 2 needed `$lookup`, and the obvious `$group` gave a wrong answer without any error.
- **Constraints.** MySQL refused bad data on its own: an unknown planet for a moon, a duplicate moon, a value outside the `ENUM`. In MongoDB none of that is checked until we add indexes and validation ourselves (sections 10 to 12).
- **For this data**, I'd choose **MySQL**, or MongoDB with embedded moons. The data is small, has a fixed shape and is fully related, and SQL answers new questions without redesigning anything. Nothing here needs to be spread over many machines. MongoDB starts to pay off when the records vary in shape, are read as a whole, or grow beyond one server: add each planet's atmosphere layers, mission history and changing lists of instruments, all different from planet to planet, and the document model looks much more attractive.

## 9. Reading and writing documents

The rest of the examples use these two databases. In mongosh, `use solar_embedded` or `use solar_referenced` switches between them.

### Finding documents

`find()` takes a **filter** (which documents) and an optional **projection** (which fields). Query operators start with `$`: `$gt` (greater than), `$gte`, `$lt`, `$lte`, `$ne` (not equal), `$in` (any of a list) and so on.

```javascript
use solar_embedded
db.planets.find({ diameter: { $gt: 12000 } }, { diameter: 1 })
```

```text
[
  { _id: 'Venus', diameter: 12104 },
  { _id: 'Earth', diameter: 12756 },
  { _id: 'Jupiter', diameter: 142984 },
  { _id: 'Saturn', diameter: 120536 },
  { _id: 'Uranus', diameter: 51118 },
  { _id: 'Neptune', diameter: 49528 }
]
```

`find()` returns **every** matching document (`findOne()` returns one). In the projection, `1` means "include this field"; `_id` comes back unless you exclude it with `_id: 0`.

Sorting (`1` ascending, `-1` descending), limiting and counting:

```javascript
db.planets.find({ dayLength: { $lt: 24 } }, { dayLength: 1 }).sort({ dayLength: 1 })
```

```text
[
  { _id: 'Jupiter', dayLength: 10 },
  { _id: 'Saturn', dayLength: 11 },
  { _id: 'Neptune', dayLength: 16 },
  { _id: 'Uranus', dayLength: 17 }
]
```

```javascript
db.planets.find({}, { diameter: 1 }).sort({ diameter: -1 }).limit(3)
```

```text
[
  { _id: 'Jupiter', diameter: 142984 },
  { _id: 'Saturn', diameter: 120536 },
  { _id: 'Uranus', diameter: 51118 }
]
```

```javascript
db.planets.countDocuments({ yearLength: { $gt: 400 } })
```

```text
5
```

A projection with `0` means "everything except": here, every field but the moons. `$in` matches any value in a list:

```javascript
db.planets.find({ _id: { $in: ["Earth", "Mars"] } }, { moons: 0 })
```

```text
[
  { _id: 'Earth', diameter: 12756, dayLength: 24, yearLength: 365 },
  { _id: 'Mars', diameter: 6792, dayLength: 25, yearLength: 687 }
]
```

Fields listed side by side in a filter are combined with AND; for OR, use `$or` with a list of conditions:

```javascript
db.planets.find(
  { $or: [{ dayLength: { $gt: 1000 } }, { diameter: { $gt: 100000 } }] },
  { _id: 1 }
)
```

```text
[
  { _id: 'Mercury' },
  { _id: 'Venus' },
  { _id: 'Jupiter' },
  { _id: 'Saturn' }
]
```

### Querying inside arrays

Here's something SQL has no direct equivalent for. A condition on an array field matches if **any element** matches, and dot notation reaches into the objects inside it:

```javascript
db.planets.find({ "moons.name": "Titan" }, { _id: 1 })
```

```text
[ { _id: 'Saturn' } ]
```

```javascript
db.planets.find({ "moons.liquidWater": "Yes" }, { _id: 1 })
```

```text
[ { _id: 'Jupiter' }, { _id: 'Saturn' } ]
```

Notice what came back: **planets**, not moons. `find()` always returns whole documents (or the fields you project). The positional projection `"moons.$"` keeps only the **first** matching moon of each planet, so Ganymede and Enceladus are missing:

```javascript
db.planets.find({ "moons.liquidWater": "Yes" }, { "moons.$": 1 })
```

```text
[
  { _id: 'Jupiter', moons: [ { name: 'Europa', liquidWater: 'Yes' } ] },
  { _id: 'Saturn', moons: [ { name: 'Titan', liquidWater: 'Yes' } ] }
]
```

To get every matching moon, use `$unwind` in an aggregation, as in section 8. `$size` tests an array's length:

```javascript
db.planets.find({ moons: { $size: 0 } }, { _id: 1 })
```

```text
[ { _id: 'Mercury' }, { _id: 'Venus' } ]
```

### Regular expressions

MongoDB accepts JavaScript-style regular expressions in a filter. A regular expression matches **anywhere** in the value unless you **anchor** it: `^` for the start, `$` for the end. These examples use the referenced moons:

```javascript
use solar_referenced
db.moons.find({ name: /^T/ }, { _id: 0, name: 1 })
```

```text
[ { name: 'Titan' }, { name: 'Titania' }, { name: 'Triton' } ]
```

```javascript
db.moons.find({ name: /us$/ }, { _id: 0, name: 1 })
```

```text
[ { name: 'Enceladus' }, { name: 'Iapetus' }, { name: 'Proteus' } ]
```

The `i` flag ignores case. Unanchored, `/titan/i` also finds Titania; anchored at both ends, it finds only Titan:

```javascript
db.moons.find({ name: /titan/i }, { _id: 0, name: 1 })
```

```text
[ { name: 'Titan' }, { name: 'Titania' } ]
```

```javascript
db.moons.find({ name: /^titan$/i }, { _id: 0, name: 1 })
```

```text
[ { name: 'Titan' } ]
```

#### A common mistake: `/*` starts a comment

A pattern like "anything, then titan, then anything" isn't written `/*titan*/`. In JavaScript, and so in mongosh, `/*` starts a **comment** that runs to the next `*/`, so the whole pattern disappears and the command is broken:

```javascript
db.moons.find({ name: /*[tT]itan*/ })
```

```text
Uncaught:
SyntaxError: Unexpected token (1:35)

> 1 | db.moons.find({ name: /*[tT]itan*/ })
    |                                    ^
  2 |
```

In a regular expression, `*` means "repeat the thing before me", so it can't come first. You don't need it anyway, because an unanchored pattern already matches anywhere in the name:

```javascript
db.moons.find({ name: /[tT]itan/ }, { _id: 0, name: 1 })
```

```text
[ { name: 'Titan' }, { name: 'Titania' } ]
```

If you want to write "anything" explicitly, it's `.*`, as in `/.*titan.*/i`.

### Updating documents

An update has two parts: a **filter** (like SQL's `WHERE`) and an **update document** that says what to change, using update operators such as `$set`, `$unset`, `$inc` (add to a number) and `$push` (append to an array). `updateOne()` changes the first matching document, `updateMany()` all of them.

Adding a new field to some documents needs no `ALTER TABLE`:

```javascript
use solar_embedded
db.planets.updateMany(
  { _id: { $in: ["Mercury", "Venus", "Earth", "Mars"] } },
  { $set: { type: "terrestrial" } }
)
db.planets.updateMany({ type: { $exists: false } }, { $set: { type: "giant" } })
```

```text
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 4,
  modifiedCount: 4,
  upsertedCount: 0
}
```

The first `updateMany()` reports 4 matched and 4 modified; so does the second, which gives the other four planets `type: "giant"`.

Adding a moon to a planet is a `$push` onto its array. Despina is one of Neptune's smaller moons (NASA's [Neptune moons](https://science.nasa.gov/neptune/moons/) page); like the other small moons in our data, its water is "Unknown":

```javascript
db.planets.updateOne(
  { _id: "Neptune" },
  { $push: { moons: { name: "Despina", liquidWater: "Unknown" } } }
)
```

```text
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
```

Updating **one element inside an array** needs care. Say we decide to relabel "Unknown" as "Not known". The **positional operator** `$` updates the element that matched the filter, but only the **first** one in each document:

```javascript
db.planets.updateOne(
  { _id: "Saturn", "moons.liquidWater": "Unknown" },
  { $set: { "moons.$.liquidWater": "Not known" } }
)
db.planets.findOne({ _id: "Saturn" }, { moons: 1 })
```

```text
{
  _id: 'Saturn',
  moons: [
    { name: 'Titan', liquidWater: 'Yes' },
    { name: 'Enceladus', liquidWater: 'Yes' },
    { name: 'Rhea', liquidWater: 'Not known' },
    { name: 'Iapetus', liquidWater: 'Unknown' },
    { name: 'Dione', liquidWater: 'Suspected' }
  ]
}
```

Rhea changed, Iapetus didn't. To change **every** matching element, give the elements a name in `arrayFilters` and use `$[name]`:

```javascript
db.planets.updateMany(
  { "moons.liquidWater": "Unknown" },
  { $set: { "moons.$[m].liquidWater": "Not known" } },
  { arrayFilters: [{ "m.liquidWater": "Unknown" }] }
)
```

```text
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 2,
  modifiedCount: 2,
  upsertedCount: 0
}
```

```javascript
db.planets.find({ "moons.liquidWater": "Not known" }, { "moons.name": 1, "moons.liquidWater": 1 })
```

```text
[
  {
    _id: 'Saturn',
    moons: [
      { name: 'Titan', liquidWater: 'Yes' },
      { name: 'Enceladus', liquidWater: 'Yes' },
      { name: 'Rhea', liquidWater: 'Not known' },
      { name: 'Iapetus', liquidWater: 'Not known' },
      { name: 'Dione', liquidWater: 'Suspected' }
    ]
  },
  {
    _id: 'Neptune',
    moons: [
      { name: 'Triton', liquidWater: 'Suspected' },
      { name: 'Nereid', liquidWater: 'Not known' },
      { name: 'Proteus', liquidWater: 'Not known' },
      { name: 'Larissa', liquidWater: 'Not known' },
      { name: 'Despina', liquidWater: 'Not known' }
    ]
  }
]
```

Now all six moons that were "Unknown", on both planets, read "Not known". (There's also `$[]`, with nothing between the brackets, which updates every element of the array whatever its value.) Let's put the labels back:

```javascript
db.planets.updateMany(
  { "moons.liquidWater": "Not known" },
  { $set: { "moons.$[m].liquidWater": "Unknown" } },
  { arrayFilters: [{ "m.liquidWater": "Not known" }] }
)
```

In SQL each moon is its own row, so `UPDATE Moons SET HasLiquidWater = 'Not known' WHERE HasLiquidWater = 'Unknown'` would have done it in one go. Embedding makes reading a planet easy and changing things deep inside it a little harder.

### Upserts and deletes

An **upsert** updates a matching document or inserts one if none matches. Let's add Pluto (figures from the same NASA fact sheet):

```javascript
use solar_embedded
db.planets.updateOne(
  { _id: "Pluto" },
  { $set: { diameter: 2376, dayLength: 153, yearLength: 90560, moons: [] } },
  { upsert: true }
)
```

```text
{
  acknowledged: true,
  insertedId: 'Pluto',
  matchedCount: 0,
  modifiedCount: 0,
  upsertedCount: 1
}
```

We didn't give Pluto a `type`, which shows a difference from SQL's `NULL`. A filter for `null` matches documents where the field is `null` **or missing**; `$exists: false` matches only documents without it:

```javascript
db.planets.find({ type: null }, { _id: 1 })
db.planets.find({ type: { $exists: false } }, { _id: 1 })
```

```text
[ { _id: 'Pluto' } ]
[ { _id: 'Pluto' } ]
```

Pluto was reclassified as a dwarf planet in 2006 by the International Astronomical Union ([NASA: Pluto facts](https://science.nasa.gov/dwarf-planets/pluto/facts/)), so it can go again:

```javascript
db.planets.deleteOne({ _id: "Pluto" })
db.planets.countDocuments()
```

```text
{ acknowledged: true, deletedCount: 1 }
8
```

`deleteMany()` deletes every match, and `deleteMany({})` empties a collection, so read your filter twice.

## 10. Surprises for SQL developers

Each of these is shown in both databases, MySQL first.

### 1. A misspelt field is accepted

```sql
UPDATE Planets SET Diamter = 6800 WHERE PlanetName = 'Mars';
```

```text
ERROR 1054 (42S22): Unknown column 'Diamter' in 'field list'
```

```javascript
use solar_embedded
db.planets.updateOne({ _id: "Mars" }, { $set: { diamter: 6800 } })
db.planets.findOne({ _id: "Mars" }, { diameter: 1, diamter: 1 })
```

```text
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 1,
  modifiedCount: 1,
  upsertedCount: 0
}
{ _id: 'Mars', diameter: 6792, diamter: 6800 }
```

MySQL knows its columns and refuses. MongoDB has no list of fields, so it happily added a second, misspelt one to Mars. Tidy up with `$unset`:

```javascript
db.planets.updateOne({ _id: "Mars" }, { $unset: { diamter: "" } })
```

### 2. A misspelt collection is created, not rejected

```sql
SELECT * FROM Planet WHERE PlanetName = 'Mars';
```

```text
ERROR 1146 (42S02): Table 'Astronomy.Planet' doesn't exist
```

In MongoDB, reading a collection that doesn't exist just returns nothing (the shell prints no result at all), and writing to it creates it:

```javascript
db.planet.find({ _id: "Mars" })
db.planet.insertOne({ _id: "Mars" })
db.getCollectionNames()
```

```text
{ acknowledged: true, insertedId: 'Mars' }
[ 'planets', 'planet' ]
```

We now have a `planet` collection next to `planets`. Remove it with `db.planet.drop()`.

### 3. Comparisons are case-sensitive

```sql
SELECT PlanetName, MoonName FROM Moons WHERE MoonName = 'titan';
```

```text
+------------+----------+
| PlanetName | MoonName |
+------------+----------+
| Saturn     | Titan    |
+------------+----------+
1 row in set
```

MySQL's default collation (`utf8mb4_0900_ai_ci`) ignores case and accents, so `'titan'` finds Titan. MongoDB compares strings exactly unless you ask for a **collation**; `strength: 2` ignores case:

```javascript
db.planets.find({ "moons.name": "titan" }, { _id: 1 })
```

That prints nothing: no match. With a collation:

```javascript
db.planets.find({ "moons.name": "titan" }, { _id: 1 }).collation({ locale: "en", strength: 2 })
```

```text
[ { _id: 'Saturn' } ]
```

### 4. Types are not converted for you

```sql
SELECT PlanetName FROM Planets WHERE Diameter = '12756';
```

```text
+------------+
| PlanetName |
+------------+
| Earth      |
+------------+
1 row in set
```

MySQL converted the string `'12756'` to a number. In MongoDB the string `"12756"` and the number `12756` are different values, so only the second query finds Earth:

```javascript
db.planets.find({ diameter: "12756" }, { _id: 1 })
db.planets.find({ diameter: 12756 }, { _id: 1 })
```

```text
[ { _id: 'Earth' } ]
```

This bites when data arrives from a web form or a CSV file as text.

### 5. `null` also means "missing"

As we saw with Pluto in section 9, `{ type: null }` matches documents where `type` is `null` **and** documents with no `type` at all. Use `{ type: { $exists: false } }` when you mean "missing". (In SQL, remember, `WHERE Type = NULL` matches nothing; you need `IS NULL`.)

### 6. References aren't enforced

```sql
DELETE FROM Planets WHERE PlanetName = 'Neptune';
```

```text
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`Astronomy`.`Moons`, CONSTRAINT `Moons_ibfk_1` FOREIGN KEY (`PlanetName`) REFERENCES `Planets` (`PlanetName`))
```

The foreign key protects the moons. MongoDB has no foreign keys, so in the referenced design deleting Neptune leaves its four moons pointing at nothing:

```javascript
use solar_referenced
db.planets.deleteOne({ _id: "Neptune" })
db.moons.countDocuments({ planet: "Neptune" })
```

```text
{ acknowledged: true, deletedCount: 1 }
4
```

Keeping references valid is your application's job (or avoid the problem by embedding). Let's put Neptune back:

```javascript
db.planets.insertOne({ _id: "Neptune", diameter: 49528, dayLength: 16, yearLength: 59800 })
```

### 7. Uniqueness needs an index

```sql
INSERT INTO Moons VALUES ('Neptune', 'Triton', 'Suspected');
```

```text
ERROR 1062 (23000): Duplicate entry 'Neptune-Triton' for key 'Moons.PRIMARY'
```

The composite primary key stops a duplicate moon. In MongoDB only `_id` is unique, and the moons' `_id`s are generated, so a second Triton goes straight in:

```javascript
db.moons.insertOne({ name: "Triton", planet: "Neptune", liquidWater: "Suspected" })
db.moons.countDocuments({ name: "Triton" })
```

```text
2
```

The fix is a **unique index**, MongoDB's version of a `UNIQUE` constraint. Delete the duplicate first, or the index can't be built:

```javascript
db.moons.deleteOne({ name: "Triton", liquidWater: "Suspected" })
db.moons.createIndex({ planet: 1, name: 1 }, { unique: true })
db.moons.insertOne({ name: "Triton", planet: "Neptune", liquidWater: "Suspected" })
```

```text
planet_1_name_1
Uncaught MongoServerError: E11000 duplicate key error collection: solar_referenced.moons index: planet_1_name_1 dup key: { planet: "Neptune", name: "Triton" }
```

### 8. `_id` can't be changed

A document's `_id` is fixed once it's written. Using the planet's name as `_id` is convenient, but renaming a planet would mean inserting a new document and deleting the old one (and, in the referenced design, updating every moon that points at it). In SQL, `ON UPDATE CASCADE` can do this for you. If a natural key might change, let MongoDB generate the `_id` and keep the name in an ordinary field.

## 11. Indexes and `explain()`

Indexes work much as they do in MySQL: without one, MongoDB reads every document in the collection (a **collection scan**); with one, it goes straight to the matching documents. `explain("executionStats")` shows which plan was used, like MySQL's `EXPLAIN`:

```javascript
use solar_referenced
const before = db.moons.find({ liquidWater: "Yes" }).explain("executionStats")
before.queryPlanner.winningPlan
```

```text
{
  isCached: false,
  stage: 'COLLSCAN',
  filter: { liquidWater: { '$eq': 'Yes' } },
  nss: 'solar_referenced.moons',
  direction: 'forward'
}
```

```javascript
before.executionStats.totalDocsExamined
```

```text
20
```

`COLLSCAN`, and all 20 moons examined to find 4. Now add an index and ask again:

```javascript
db.moons.createIndex({ liquidWater: 1 })
const after = db.moons.find({ liquidWater: "Yes" }).explain("executionStats")
after.queryPlanner.winningPlan
```

```text
liquidWater_1
{
  isCached: false,
  stage: 'FETCH',
  nss: 'solar_referenced.moons',
  inputStage: {
    stage: 'IXSCAN',
    nss: 'solar_referenced.moons',
    keyPattern: { liquidWater: 1 },
    indexName: 'liquidWater_1',
    isMultiKey: false,
    multiKeyPaths: { liquidWater: [] },
    isUnique: false,
    isSparse: false,
    isPartial: false,
    indexVersion: 2,
    direction: 'forward',
    indexBounds: { liquidWater: [ '["Yes", "Yes"]' ] }
  }
}
```

```javascript
after.executionStats.totalDocsExamined
```

```text
4
```

An `IXSCAN` (index scan) followed by `FETCH` (read the documents), and only the 4 matching moons examined. With 20 documents it makes no difference to speed; with 20 million it's the difference between milliseconds and minutes. `getIndexes()` lists them all, including the automatic one on `_id`:

```javascript
db.moons.getIndexes()
```

```text
[
  { v: 2, key: { _id: 1 }, name: '_id_' },
  {
    v: 2,
    key: { planet: 1, name: 1 },
    name: 'planet_1_name_1',
    unique: true
  },
  { v: 2, key: { liquidWater: 1 }, name: 'liquidWater_1' }
]
```

An index on an array field (like `moons.name` in the embedded design) is a **multikey index**: it has an entry for every element, so queries inside arrays can use it too.

## 12. Schema validation: rules when you want them

"No schema" is MongoDB's default, not a rule. You can attach a **validator** to a collection, written in JSON Schema, and MongoDB will then reject inserts and updates that break it. This one says every moon needs a `name`, a `planet` and a `liquidWater` value from our four categories, much like the `NOT NULL` and `ENUM` in the SQL table:

```javascript
use solar_referenced
db.runCommand({
  collMod: "moons",
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["name", "planet", "liquidWater"],
      properties: {
        name: { bsonType: "string" },
        planet: { bsonType: "string" },
        liquidWater: { enum: ["Yes", "No", "Unknown", "Suspected"] }
      }
    }
  }
})
```

```javascript
db.moons.insertOne({ name: "Despina", planet: "Neptune", liquidWater: "Maybe" })
```

```text
Uncaught:
MongoServerError: Document failed validation
Additional information: {
  failingDocumentId: ObjectId('6afe671b4332e031cad1c58d'),
  details: {
    operatorName: '$jsonSchema',
    schemaRulesNotSatisfied: [
      {
        operatorName: 'properties',
        propertiesNotSatisfied: [
          {
            propertyName: 'liquidWater',
            details: [
              {
                operatorName: 'enum',
                specifiedAs: { enum: [ 'Yes', 'No', 'Unknown', 'Suspected' ] },
                reason: 'value was not found in enum',
                consideredValue: 'Maybe'
              }
            ]
          }
        ]
      }
    ]
  }
}
```

The error says exactly which rule failed. A validator is a middle way: you choose which fields to check, and the rest of the document stays flexible. You can add one to `db.createCollection()` from the start, too.

## 13. Transactions: ACID in MongoDB

If you've met ACID transactions (atomic, consistent, isolated, durable) in SQL, the first question is whether MongoDB has them. It does, in two ways.

**A single document is always atomic.** An update to one document either happens completely or not at all, however many fields and array elements it touches. That's why embedding matters so much: in `solar_embedded`, adding Despina to Neptune's `moons` was one atomic operation. MongoDB's own advice is that a good document design removes the need for most multi-document transactions ([MongoDB Manual: Transactions](https://www.mongodb.com/docs/manual/core/transactions/)).

**Across documents and collections you need a transaction.** In `solar_referenced` the moons and planets are separate. Suppose each planet keeps a `moonCount` of the moons we store (a copied value, kept for fast reads); adding a moon now means two writes that must succeed or fail together. First, set the counts:

```javascript
use solar_referenced
db.planets.find().forEach(p =>
  db.planets.updateOne(
    { _id: p._id },
    { $set: { moonCount: db.moons.countDocuments({ planet: p._id }) } }
  )
)
db.planets.findOne({ _id: "Neptune" })
```

```text
{
  _id: 'Neptune',
  diameter: 49528,
  dayLength: 16,
  yearLength: 59800,
  moonCount: 4
}
```

A transaction belongs to a **session**. Inside it, use the database you get from the session (`tx` here):

```javascript
const session = db.getMongo().startSession()
const tx = session.getDatabase("solar_referenced")
session.startTransaction()
tx.moons.insertOne({ name: "Despina", planet: "Neptune", liquidWater: "Unknown" })
tx.planets.updateOne({ _id: "Neptune" }, { $inc: { moonCount: 1 } })
```

Before committing, look from **outside** the transaction:

```javascript
db.moons.countDocuments({ name: "Despina" })
```

```text
0
```

Nobody else can see Despina yet (isolation). Commit, then look again:

```javascript
session.commitTransaction()
db.moons.countDocuments({ name: "Despina" })
db.planets.findOne({ _id: "Neptune" }, { moonCount: 1 })
```

```text
1
{ _id: 'Neptune', moonCount: 5 }
```

Now a transaction that fails halfway. It increments the count, then tries to insert Despina again, which the unique index from section 10 refuses:

```javascript
session.startTransaction()
tx.planets.updateOne({ _id: "Neptune" }, { $inc: { moonCount: 1 } })
tx.moons.insertOne({ name: "Despina", planet: "Neptune", liquidWater: "Unknown" })
```

```text
Uncaught MongoServerError: E11000 duplicate key error collection: solar_referenced.moons index: planet_1_name_1 dup key: { planet: "Neptune", name: "Despina" }
```

```javascript
session.abortTransaction()
db.planets.findOne({ _id: "Neptune" }, { moonCount: 1 })
session.endSession()
```

```text
{ _id: 'Neptune', moonCount: 5 }
```

Still 5: the increment was rolled back with the rest of the transaction (atomicity).

Points to know:

- Multi-document transactions need a **replica set** (MongoDB 4.0 and later) or a **sharded cluster** (4.2 and later); on a standalone server you'll get an error. That's why section 4 started a one-member replica set.
- They cost more than single-document writes, and by default a transaction must finish in under a minute or it's aborted ([Production considerations](https://www.mongodb.com/docs/manual/core/transactions-production-consideration/)), so keep them short.
- With MongoDB's default write concern, `majority`, a write is only acknowledged once most members of the replica set have it, so it survives the loss of the primary. That's the trade-off from part 1's CAP section: waiting for copies costs speed but keeps the data consistent.

## 14. MapReduce then and now (and Hadoop)

![MapReduce and the aggregation pipeline side by side: map emits one (water category, 1) pair per moon, shuffle groups the pairs by key, reduce adds them up; in a pipeline, $unwind plays the part of map and $group the part of reduce](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/a86487f4-6b31-46a8-b22d-870fb567b0a2.png)

In part 1 we met **MapReduce**: a **map** step that runs where the data is and turns each record into key/value pairs, and a **reduce** step that combines all the values for one key. Counting our moons by water category is a classic example. As an aggregation pipeline, `$unwind` does the mapping (one document per moon) and `$group` the reducing:

```javascript
use solar_embedded
db.planets.aggregate([
  { $unwind: "$moons" },
  { $group: { _id: "$moons.liquidWater", moons: { $sum: 1 } } },
  { $sort: { moons: -1, _id: 1 } }
])
```

```text
[
  { _id: 'Suspected', moons: 7 },
  { _id: 'Unknown', moons: 6 },
  { _id: 'No', moons: 4 },
  { _id: 'Yes', moons: 4 }
]
```

(21 moons now, with Despina.) MongoDB also has a `mapReduce` command, where you write the two functions yourself in JavaScript. It still runs in MongoDB 9.0, but it has been **deprecated since MongoDB 5.0**, and mongosh tells you so:

```javascript
db.planets.mapReduce(
  function () { this.moons.forEach(m => emit(m.liquidWater, 1)); },
  function (key, values) { return Array.sum(values); },
  { out: { inline: 1 } }
)
```

```text
DeprecationWarning: Collection.mapReduce() is deprecated. Use an aggregation instead.
See https://mongodb.com/docs/manual/core/map-reduce for details.
{
  results: [
    { _id: 'Unknown', value: 6 },
    { _id: 'Yes', value: 4 },
    { _id: 'No', value: 4 },
    { _id: 'Suspected', value: 7 }
  ],
  ok: 1,
  '$clusterTime': {
    clusterTime: Timestamp({ t: 1795057436, i: 2 }),
    signature: {
      hash: Binary.createFromBase64('AAAAAAAAAAAAAAAAAAAAAAAAAAA=', 0),
      keyId: Long('0')
    }
  },
  operationTime: Timestamp({ t: 1795057436, i: 2 })
}
```

Same counts, but MongoDB's advice is to use an aggregation pipeline instead, which is faster and easier to write ([MongoDB Manual: Map-Reduce](https://www.mongodb.com/docs/manual/reference/method/db.collection.mapReduce/)).

**Hadoop.** You'll often see MapReduce mentioned alongside **Apache Hadoop**, the best-known open-source framework built around the idea. Hadoop's core modules are **HDFS** (a distributed file system that spreads huge files over many machines), **YARN** (which schedules jobs and shares out a cluster's resources) and **Hadoop MapReduce** (which runs map and reduce jobs on YARN) ([Apache Hadoop](https://hadoop.apache.org/)). It's built for batch processing of very large files rather than for looking up individual records, so it's a different tool from MongoDB. Related projects such as **Apache Spark**, a general compute engine that can run on Hadoop data, offer more flexible ways to process it, but the map-then-reduce pattern is still everywhere, including in MongoDB's `$unwind` and `$group`.

## 15. Scaling out: replica sets and sharding

![Sharding: a router (mongos) sends each query to the right shard using the shard key. A query that includes the shard key goes to one shard (a targeted operation, like partition pruning); a query without it goes to every shard (a broadcast operation). Each shard is itself a replica set](https://cdn.hashnode.com/uploads/covers/5fdbaa361485f22e24b325e3/04c75326-924d-4844-957e-09464ced0880.png)

Part 1 showed the two tools every distributed database uses: **replication** (copies) and **partitioning** (splitting). MongoDB builds both in.

- A **replica set** is a group of `mongod` servers that keep the same data. One is the **primary** and takes the writes; the others are **secondaries** that copy them. If the primary fails, an eligible secondary calls an election and becomes the new primary ([MongoDB Manual: Replication](https://www.mongodb.com/docs/manual/replication/)). Our one-member set has no copies, but it's the same machinery.
- **Sharding** is MongoDB's horizontal partitioning. A collection is split by a **shard key** (one or more indexed fields) into ranges of values, either of the values themselves (**ranged sharding**) or of a hash of them (**hashed sharding**), and the ranges are spread over several **shards**, each of which is a replica set. Applications talk to a router, `mongos`, which sends a query that includes the shard key to just the shards that hold it (a **targeted** operation) and a query without it to all of them (a **broadcast** operation) ([MongoDB Manual: Sharding](https://www.mongodb.com/docs/manual/sharding/)).

This is where documents pay off. Because a document carries its related data with it, a query for one planet and its moons reads one document on one shard, with no join across machines. Choosing the shard key is the most important decision: it decides which queries are targeted, and a key with few different values (such as `liquidWater`, with only four) can't be split finely enough. Our eight planets would never need sharding, of course; it's for collections too big or too busy for one server.

## 16. MySQL can store documents too

The choice isn't all or nothing. MySQL has a native `JSON` column type, which checks that what you store is valid JSON and lets you query inside it. In the `Astronomy` database:

```sql
CREATE TABLE PlanetDocs (doc JSON NOT NULL);
INSERT INTO PlanetDocs (doc) VALUES
  ('{"_id": "Mars", "diameter": 6792,
     "moons": [{"name": "Phobos", "liquidWater": "No"},
               {"name": "Deimos", "liquidWater": "No"}]}'),
  ('{"_id": "Jupiter", "diameter": 142984,
     "moons": [{"name": "Io", "liquidWater": "No"},
               {"name": "Europa", "liquidWater": "Yes"},
               {"name": "Ganymede", "liquidWater": "Yes"},
               {"name": "Callisto", "liquidWater": "Suspected"}]}');
```

Invalid JSON, such as an unquoted key, is rejected:

```sql
INSERT INTO PlanetDocs (doc) VALUES ('{ _id: "Venus" }');
```

```text
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 2 in value for column 'PlanetDocs.doc'.
```

`->>` extracts a value as text, and `JSON_LENGTH()` counts the elements of an array:

```sql
SELECT doc->>'$._id' AS planet, JSON_LENGTH(doc->'$.moons') AS moons
FROM PlanetDocs;
```

```text
+---------+-------+
| planet  | moons |
+---------+-------+
| Mars    |     2 |
| Jupiter |     4 |
+---------+-------+
2 rows in set
```

`JSON_TABLE()` turns the array of moons back into rows you can filter like a table, the MySQL equivalent of `$unwind`:

```sql
SELECT jt.planet, jt.moon
FROM PlanetDocs,
     JSON_TABLE(doc, '$' COLUMNS (
         planet VARCHAR(25) PATH '$._id',
         NESTED PATH '$.moons[*]' COLUMNS (
             moon        VARCHAR(100) PATH '$.name',
             liquidWater VARCHAR(10)  PATH '$.liquidWater'
         )
     )) AS jt
WHERE jt.liquidWater = 'Yes';
```

```text
+---------+----------+
| planet  | moon     |
+---------+----------+
| Jupiter | Europa   |
| Jupiter | Ganymede |
+---------+----------+
2 rows in set
```

This works well for a few flexible attributes inside an otherwise relational design (product options, settings, data from an outside API). If most of your data is documents and you need to spread it over many servers, a document database is the better fit.

## 17. Strengths, weaknesses and choosing

| | Relational (MySQL) | Document (MongoDB) |
|---|---|---|
| **Structure** | fixed columns, declared in advance; changes need `ALTER TABLE` | flexible: documents in one collection can differ; rules optional (validation) |
| **Relationships** | foreign keys, enforced; joins are the normal way to work | embedding, or references the database doesn't check; `$lookup` when you need a join |
| **Duplication** | normalised: each fact stored once | often copied on purpose to avoid joins, so copies can drift apart |
| **Questions you didn't plan for** | easy: any join or grouping, in SQL | harder: the design follows the queries you expected |
| **Reading a whole record** | several tables joined | often one document |
| **Fit with program code** | rows mapped to objects (often by an ORM) | documents look like the objects in your program |
| **Transactions** | the default for everything | always atomic within one document; across documents, available but costlier |
| **Scaling out** | possible (part 1), but hard | replica sets and sharding built in |
| **Query language** | SQL, a standard shared by many products | MongoDB's own query API and aggregation pipeline |

A short checklist:

1. **What shape is your data?** Uniform rows with many relationships suit tables. Self-contained records of varying shape, read as a whole, suit documents.
2. **How will it be queried?** If you know the main access patterns, you can design documents around them. If people will keep asking new questions across entities, SQL is more forgiving.
3. **Which guarantees do you need?** If correctness depends on constraints across many records (money, stock, bookings), the relational model does the work for you.
4. **Do you need to distribute?** Most applications fit on one server. If yours doesn't, or must stay available across data centres, that's when MongoDB's built-in sharding and replication count most.

Many real systems use both: a relational database for orders and payments, a document database for a product catalogue or user activity, or a `JSON` column where a little flexibility is enough.

## 18. Check your understanding

1. What's wrong with this as JSON? `{ name: 'Io', liquidWater: "No", }`
2. Embed or reference? (a) The layers of a planet's atmosphere. (b) Space missions, each of which visited several planets. (c) Every observation ever recorded of each planet by a telescope network.
3. Which moons in our data does `db.moons.find({ name: /^T.*n$/ })` return?
4. In `solar_embedded`, Uranus's moons are Titania, Oberon, Umbriel and Ariel, all "Suspected". What does this change? `db.planets.updateOne({ _id: "Uranus", "moons.liquidWater": "Suspected" }, { $set: { "moons.$.liquidWater": "Yes" } })`
5. Write the MongoDB version of `SELECT HasLiquidWater, COUNT(*) FROM Moons GROUP BY HasLiquidWater` for the `moons` collection in `solar_referenced`.
6. In the referenced design, deleting a planet left its moons behind. Name two ways to stop that happening.

### Answers

1. Three things: the key `name` isn't in double quotes, `'Io'` uses single quotes, and there's a trailing comma. Valid JSON is `{ "name": "Io", "liquidWater": "No" }`.
2. (a) Embed: it belongs to one planet, it's small and it's read with the planet. (b) Reference: missions and planets are many-to-many, and embedding would copy each mission into several planets. (c) Reference: the list grows without limit and would eventually pass the 16 MiB document limit.
3. Titan and Triton: names that start with "T" and end with "n". Titania ends with "a".
4. Only Titania changes to "Yes". The positional `$` updates the first matching element in each document; `$[m]` with `arrayFilters` would change all four.
5. `db.moons.aggregate([{ $group: { _id: "$liquidWater", moons: { $sum: 1 } } }])` (add `{ $sort: { moons: -1 } }` to order it).
6. Embed the moons in their planet, so they're deleted with it; or have the application delete or check the moons (ideally in a transaction) before deleting a planet. MongoDB has no foreign keys to do it for you.

## 19. Further reading and sources

- MongoDB Manual: [SQL to MongoDB mapping chart](https://www.mongodb.com/docs/manual/reference/sql-comparison/), [Data modelling](https://www.mongodb.com/docs/manual/data-modeling/), [Query predicates](https://www.mongodb.com/docs/manual/reference/operator/query/), [Positional `$`](https://www.mongodb.com/docs/manual/reference/operator/update/positional/) and [`$[<identifier>]`](https://www.mongodb.com/docs/manual/reference/operator/update/positional-filtered/), [Aggregation pipeline](https://www.mongodb.com/docs/manual/core/aggregation-pipeline/), [Collation](https://www.mongodb.com/docs/manual/reference/collation/), [Schema validation](https://www.mongodb.com/docs/manual/core/schema-validation/), [Transactions](https://www.mongodb.com/docs/manual/core/transactions/), [Limits](https://www.mongodb.com/docs/manual/reference/limits/), [Release notes for MongoDB 9.0](https://www.mongodb.com/docs/manual/release-notes/9.0/).
- Bray, T. (ed.) (2017) *The JavaScript Object Notation (JSON) Data Interchange Format*, [RFC 8259](https://www.rfc-editor.org/rfc/rfc8259), and Ecma International, [ECMA-404](https://ecma-international.org/publications-and-standards/standards/ecma-404/).
- MySQL 9.7 Reference Manual: [The JSON data type](https://dev.mysql.com/doc/refman/9.7/en/json.html), [JSON table functions](https://dev.mysql.com/doc/refman/9.7/en/json-table-functions.html), [FOREIGN KEY constraints](https://dev.mysql.com/doc/refman/9.7/en/create-table-foreign-keys.html).
- Dean, J. and Ghemawat, S. (2004) "MapReduce: Simplified data processing on large clusters", OSDI 2004. [Google Research](https://research.google/pubs/mapreduce-simplified-data-processing-on-large-clusters/).
- [Apache Hadoop](https://hadoop.apache.org/) project page.
- Kleppmann, M. and Riccomini, C. (2026) *Designing Data-Intensive Applications*, 2nd edn. O'Reilly. Covers relational and document models, and much more, in depth.
- NASA: [Planetary Fact Sheet](https://nssdc.gsfc.nasa.gov/planetary/factsheet/), [Neptune moons](https://science.nasa.gov/neptune/moons/), [Pluto facts](https://science.nasa.gov/dwarf-planets/pluto/facts/).

Related posts on this blog: [Distributed databases: partitioning, replication and CAP](https://pedbad.hashnode.dev/distributed-databases-partitioning-replication-and-cap) (part 1), [Exploring the Cosmos](https://pedbad.hashnode.dev/exploring-the-cosmos-building-and-querying-a-planetary-database-with-mysql) (the planets database in MySQL), [Mastering Database Normalisation](https://pedbad.hashnode.dev/mastering-database-normalisation) (why relational designs avoid the duplication documents allow) and [Install MySQL on OSX Using Homebrew](https://pedbad.hashnode.dev/install-mysql-on-osx-using-homebrew).

*This is my own tutor material, not official University of London course material.*

