Skip to main content

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

Updated 49 min readMarkdown
Part of seriesDatabases and Advanced Data Techniques (CM3010)
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

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, 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, 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

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:

{ "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:

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:

{ 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 for your distribution.
  • Windows: the MSI installer from the MongoDB Community Server download page; install mongosh 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 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:

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:

rs.initiate()

Output (your timestamps will differ):

{
  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: 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

Save this as planets.sql and run it with mysql -u root -p < planets.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:

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;
+-----------+------------+-----------+----------------+
| 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
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;
+------------+-----------+
| 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

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

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:

[
  {
    "_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:

use solar_embedded
const fs = require('fs')
const planets = JSON.parse(fs.readFileSync('planets.json', 'utf8'))
db.planets.insertMany(planets)
{
  acknowledged: true,
  insertedIds: {
    '0': 'Mercury',
    '1': 'Venus',
    '2': 'Earth',
    '3': 'Mars',
    '4': 'Jupiter',
    '5': 'Saturn',
    '6': 'Uranus',
    '7': 'Neptune'
  }
}
db.planets.findOne({ _id: "Mars" })
{
  _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:

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 }))
))
db.moons.findOne({ name: "Phobos" })
{
  _id: ObjectId('6afe6711fc6e7b900dc53018'),
  name: 'Phobos',
  planet: 'Mars',
  liquidWater: 'No'
}
db.planets.countDocuments()
db.moons.countDocuments()
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:

use solar_embedded
bsonsize(db.planets.findOne({ _id: "Saturn" }))
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

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:

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 } }
])
[
  {
    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:

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.

db.planets.aggregate([
  { $project: { moonCount: { $size: "$moons" } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
[
  { _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:

db.moons.aggregate([
  { $group: { _id: "$planet", moonCount: { $sum: 1 } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
[
  { _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:

db.planets.aggregate([
  { $lookup: {
      from: "moons",
      localField: "_id",
      foreignField: "planet",
      as: "moons"
  } },
  { $project: { moonCount: { $size: "$moons" } } },
  { $sort: { moonCount: -1, _id: 1 } }
])
[
  { _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.

use solar_embedded
db.planets.find({ diameter: { $gt: 12000 } }, { diameter: 1 })
[
  { _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:

db.planets.find({ dayLength: { $lt: 24 } }, { dayLength: 1 }).sort({ dayLength: 1 })
[
  { _id: 'Jupiter', dayLength: 10 },
  { _id: 'Saturn', dayLength: 11 },
  { _id: 'Neptune', dayLength: 16 },
  { _id: 'Uranus', dayLength: 17 }
]
db.planets.find({}, { diameter: 1 }).sort({ diameter: -1 }).limit(3)
[
  { _id: 'Jupiter', diameter: 142984 },
  { _id: 'Saturn', diameter: 120536 },
  { _id: 'Uranus', diameter: 51118 }
]
db.planets.countDocuments({ yearLength: { $gt: 400 } })
5

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

db.planets.find({ _id: { $in: ["Earth", "Mars"] } }, { moons: 0 })
[
  { _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:

db.planets.find(
  { $or: [{ dayLength: { $gt: 1000 } }, { diameter: { $gt: 100000 } }] },
  { _id: 1 }
)
[
  { _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:

db.planets.find({ "moons.name": "Titan" }, { _id: 1 })
[ { _id: 'Saturn' } ]
db.planets.find({ "moons.liquidWater": "Yes" }, { _id: 1 })
[ { _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:

db.planets.find({ "moons.liquidWater": "Yes" }, { "moons.$": 1 })
[
  { _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:

db.planets.find({ moons: { $size: 0 } }, { _id: 1 })
[ { _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:

use solar_referenced
db.moons.find({ name: /^T/ }, { _id: 0, name: 1 })
[ { name: 'Titan' }, { name: 'Titania' }, { name: 'Triton' } ]
db.moons.find({ name: /us$/ }, { _id: 0, name: 1 })
[ { 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:

db.moons.find({ name: /titan/i }, { _id: 0, name: 1 })
[ { name: 'Titan' }, { name: 'Titania' } ]
db.moons.find({ name: /^titan$/i }, { _id: 0, name: 1 })
[ { 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:

db.moons.find({ name: /*[tT]itan*/ })
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:

db.moons.find({ name: /[tT]itan/ }, { _id: 0, name: 1 })
[ { 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:

use solar_embedded
db.planets.updateMany(
  { _id: { $in: ["Mercury", "Venus", "Earth", "Mars"] } },
  { $set: { type: "terrestrial" } }
)
db.planets.updateMany({ type: { $exists: false } }, { $set: { type: "giant" } })
{
  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 page); like the other small moons in our data, its water is "Unknown":

db.planets.updateOne(
  { _id: "Neptune" },
  { $push: { moons: { name: "Despina", liquidWater: "Unknown" } } }
)
{
  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:

db.planets.updateOne(
  { _id: "Saturn", "moons.liquidWater": "Unknown" },
  { $set: { "moons.$.liquidWater": "Not known" } }
)
db.planets.findOne({ _id: "Saturn" }, { moons: 1 })
{
  _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]:

db.planets.updateMany(
  { "moons.liquidWater": "Unknown" },
  { $set: { "moons.$[m].liquidWater": "Not known" } },
  { arrayFilters: [{ "m.liquidWater": "Unknown" }] }
)
{
  acknowledged: true,
  insertedId: null,
  matchedCount: 2,
  modifiedCount: 2,
  upsertedCount: 0
}
db.planets.find({ "moons.liquidWater": "Not known" }, { "moons.name": 1, "moons.liquidWater": 1 })
[
  {
    _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:

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):

use solar_embedded
db.planets.updateOne(
  { _id: "Pluto" },
  { $set: { diameter: 2376, dayLength: 153, yearLength: 90560, moons: [] } },
  { upsert: true }
)
{
  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:

db.planets.find({ type: null }, { _id: 1 })
db.planets.find({ type: { $exists: false } }, { _id: 1 })
[ { _id: 'Pluto' } ]
[ { _id: 'Pluto' } ]

Pluto was reclassified as a dwarf planet in 2006 by the International Astronomical Union (NASA: Pluto facts), so it can go again:

db.planets.deleteOne({ _id: "Pluto" })
db.planets.countDocuments()
{ 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

UPDATE Planets SET Diamter = 6800 WHERE PlanetName = 'Mars';
ERROR 1054 (42S22): Unknown column 'Diamter' in 'field list'
use solar_embedded
db.planets.updateOne({ _id: "Mars" }, { $set: { diamter: 6800 } })
db.planets.findOne({ _id: "Mars" }, { diameter: 1, diamter: 1 })
{
  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:

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

2. A misspelt collection is created, not rejected

SELECT * FROM Planet WHERE PlanetName = 'Mars';
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:

db.planet.find({ _id: "Mars" })
db.planet.insertOne({ _id: "Mars" })
db.getCollectionNames()
{ 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

SELECT PlanetName, MoonName FROM Moons WHERE MoonName = 'titan';
+------------+----------+
| 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:

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

That prints nothing: no match. With a collation:

db.planets.find({ "moons.name": "titan" }, { _id: 1 }).collation({ locale: "en", strength: 2 })
[ { _id: 'Saturn' } ]

4. Types are not converted for you

SELECT PlanetName FROM Planets WHERE Diameter = '12756';
+------------+
| 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:

db.planets.find({ diameter: "12756" }, { _id: 1 })
db.planets.find({ diameter: 12756 }, { _id: 1 })
[ { _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

DELETE FROM Planets WHERE PlanetName = 'Neptune';
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:

use solar_referenced
db.planets.deleteOne({ _id: "Neptune" })
db.moons.countDocuments({ planet: "Neptune" })
{ acknowledged: true, deletedCount: 1 }
4

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

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

7. Uniqueness needs an index

INSERT INTO Moons VALUES ('Neptune', 'Triton', 'Suspected');
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' _ids are generated, so a second Triton goes straight in:

db.moons.insertOne({ name: "Triton", planet: "Neptune", liquidWater: "Suspected" })
db.moons.countDocuments({ name: "Triton" })
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:

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" })
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:

use solar_referenced
const before = db.moons.find({ liquidWater: "Yes" }).explain("executionStats")
before.queryPlanner.winningPlan
{
  isCached: false,
  stage: 'COLLSCAN',
  filter: { liquidWater: { '$eq': 'Yes' } },
  nss: 'solar_referenced.moons',
  direction: 'forward'
}
before.executionStats.totalDocsExamined
20

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

db.moons.createIndex({ liquidWater: 1 })
const after = db.moons.find({ liquidWater: "Yes" }).explain("executionStats")
after.queryPlanner.winningPlan
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"]' ] }
  }
}
after.executionStats.totalDocsExamined
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:

db.moons.getIndexes()
[
  { 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:

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"] }
      }
    }
  }
})
db.moons.insertOne({ name: "Despina", planet: "Neptune", liquidWater: "Maybe" })
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).

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:

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" })
{
  _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):

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:

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

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

session.commitTransaction()
db.moons.countDocuments({ name: "Despina" })
db.planets.findOne({ _id: "Neptune" }, { moonCount: 1 })
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:

session.startTransaction()
tx.planets.updateOne({ _id: "Neptune" }, { $inc: { moonCount: 1 } })
tx.moons.insertOne({ name: "Despina", planet: "Neptune", liquidWater: "Unknown" })
Uncaught MongoServerError: E11000 duplicate key error collection: solar_referenced.moons index: planet_1_name_1 dup key: { planet: "Neptune", name: "Despina" }
session.abortTransaction()
db.planets.findOne({ _id: "Neptune" }, { moonCount: 1 })
session.endSession()
{ _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), 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

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:

use solar_embedded
db.planets.aggregate([
  { $unwind: "$moons" },
  { $group: { _id: "$moons.liquidWater", moons: { $sum: 1 } } },
  { $sort: { moons: -1, _id: 1 } }
])
[
  { _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:

db.planets.mapReduce(
  function () { this.moons.forEach(m => emit(m.liquidWater, 1)); },
  function (key, values) { return Array.sum(values); },
  { out: { inline: 1 } }
)
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).

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). 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

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). 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).

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:

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:

INSERT INTO PlanetDocs (doc) VALUES ('{ _id: "Venus" }');
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:

SELECT doc->>'$._id' AS planet, JSON_LENGTH(doc->'$.moons') AS moons
FROM PlanetDocs;
+---------+-------+
| 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:

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';
+---------+----------+
| 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

Related posts on this blog: Distributed databases: partitioning, replication and CAP (part 1), Exploring the Cosmos (the planets database in MySQL), Mastering Database Normalisation (why relational designs avoid the duplication documents allow) and Install MySQL on OSX Using Homebrew.

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

About the author

  • Head of eLearning at the University of Cambridge Language Centre, full-stack developer and AI engineer, and online tutor on the BSc Computer Science from Goldsmiths, University of London, on Coursera. I started out in neuroscience research before moving into web development, and have spent over twenty years building digital learning at Cambridge. This blog is my supplementary material for students on the BSc: set-up guides, explanations of the ideas that trip people up, and worked examples. New here? Click Website above for my Start here page and module guides.

No comments yet. Be the first to comment.

Databases and Advanced Data Techniques (CM3010)

Part 9 of 9

University of London - Module Specifications for CM3010 - Databases and Advanced Data Techniques

Start from the beginningDatabases and Advanced Data TechniquesModule Specification