Work with Dates, Arrays, and Nested Data in MongoDB

Table of Contents

1. Introduction and Overview
2. Querying and Manipulating Dates in MongoDB

• Fundamental Concepts About Dates
• Demo: Environment Configuration
• Demo: Filter by date with ISODate
• Demo: Extract date components for analysis
• Demo: Group by time period
• Demo: Date calculations with $subtract and $dateDiff
• Demo: Format dates for reporting
• Query patterns for dates

3. Querying and Analyzing Data Stored in Arrays

• Fundamental concepts about arrays
• Demo: Matching values in an array with $in
• Demo: Filtering arrays with $elemMatch
• Demo: Filter by array length with $size
• Demo: Unrolling arrays in aggregation with $unwind
• Demo: Combine $unwind and $group for analysis
• Array query patterns

4. Accessing and Reshaping Nested Fields

• Fundamental concepts about nested fields
• Demo: Query nested fields with dot notation
• Demo: Project and flatten nested values
• Demo: Combine $match and $project on nested fields
• Demo: Troubleshooting nesting inconsistencies
• Query patterns for nested structures

5. Solving Real world Query Challenges as an Analyst

• Actual Analysis Module Overview
• Demo: Date filters combined with user attributes
• Demo: Aggregate values from arrays
• Demo: Group activity by time intervals
• Demo: Flatten nested structures for reporting
• Demo: Format data for external use
• Query patterns for complex workflows

6. General summary and good practices

1. Introduction and Overview

Working with dates, arrays, and nested data in MongoDB opens the door to questions that go far beyond flat records. These features give structure and meaning to your queries, helping you uncover patterns and insights that would otherwise remain hidden.

The central philosophy of this course can be summarized as follows:

In MongoDB, dates mark the times, arrays contain the choices, and nested data tell the story. These three elements allow us to ask questions that go well beyond flat records and single values.

This course assumes familiarity with the basics of MongoDB. For complete beginners, the Up and Running with MongoDB for Data Analysts course provides a good foundation.

2. Querying and Manipulating Dates in MongoDB

Basic concepts about dates

Before getting into the commands, it is essential to understand how MongoDB handles dates internally.

• Storing in UTC: Dates are stored in UTC by default. Time zones only come into play when formatting.
• 64-bit internal format: Internally, a date is saved as a 64-bit integer counting milliseconds since January 1, 1970 (Unix epoch).
• Range Covered: Using this format, MongoDB can represent an incredible range, covering 290 million years in the past and future.
• Obligation to use ISODate: If you write a date as a string without using ISODate(), MongoDB will treat it as a simple string. For it to be a real date, it must be wrapped in ISODate().

Golden rule: Always use the ISODate() function when working with dates in MongoDB.

Demo: Environment Configuration

The dataset used throughout this course is the sample dataset of MongoDB Atlas, in particular the sample_mflix database.

Configuration steps:

1. From the MongoDB Atlas dashboard, select the cluster.
2. Click on the Load Sample Dataset option.
3. Atlas adds ready-to-use collections: movies, comments, users, etc.
4. Wait for confirmation that loading is complete.
5. Check in the Data Explorer by navigating to the sample_mflix database.
6. The movies collection is available and ready to be queried.

Demo: Filter by date with ISODate

This demo illustrates how MongoDB handles queries on dates, with the movies collection of the sample_mflix database.

Example 1 — Films released after 2010

Find films released in the modern era, after January 1, 2010:

use sample_mflix

db.movies.find(
  { released: { $gte: ISODate("2010-01-01") } },
  { title: 1, released: 1, _id: 0 }
).limit(5)

Result: Returns movies like Too Much Johnson (2014) and My Childhood (2013).

Example 2 — Movies released on exactly one day

Find films released exactly on January 12, 2012:

db.movies.find(
  { released: ISODate("2012-01-12T00:00:00.000Z") },
  { title: 1, released: 1, _id: 0 }
)

Result: Returns The Monkey King and Foreverland, both with exactly this release date.

Example 3 — Films released in the last 10 years (sliding window)

Use the JavaScript Date object to calculate a dynamic time window:

db.movies.find(
  { released: { $gte: new Date(Date.now() - 10*365*24*60*60*1000) } },
  { title: 1, released: 1, _id: 0 }
).limit(5)

Note: This query automatically adjusts based on the current date. Returns titles like The Danish Girl and Beasts of No Nation.

Example 4 — Common error: string vs ISODate

// INCORRECT : MongoDB traite la date comme une string — retourne 0 résultat
db.movies.find(
  { released: { $gte: "2010-01-01" } },
  { title: 1, released: 1, _id: 0 }
).limit(5)

// CORRECT : utiliser ISODate()
db.movies.find(
  { released: { $gte: ISODate("2010-01-01") } },
  { title: 1, released: 1, _id: 0 }
).limit(5)

Explanation: Without ISODate(), MongoDB compares a date to a string, which produces no valid result.

Demo: Extract date components for analysis

This section explores how to extract and reshape date information using aggregation operators.

Example 1 — Project release year

Add a releaseYear field calculated from the released field:

db.movies.aggregate([
  { $project: {
      _id: 0,
      title: 1,
      released: 1,
      releaseYear: { $year: "$released" }
  }},
  { $limit: 5 }
])

Result: Each film now has a clear releaseYear value (eg: The Great Train Robbery → 1903).

Example 2 — Decompose a date into year, month and day

db.movies.aggregate([
  { $project: {
      _id: 0,
      title: 1,
      year:  { $year: "$released" },
      month: { $month: "$released" },
      day:   { $dayOfMonth: "$released" }
  }},
  { $limit: 5 }
])

Result: A Corner in Wheat → year 1909, month 12, day 13. Winsor McCay → year 1911, month 4, day 8.

Example 3 — Count films by year of release

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $group: { _id: { year: { $year: "$released" } }, count: { $sum: 1 } } },
  { $project: { _id: 0, year: "$_id.year", count: 1 } },
  { $sort: { year: -1 } },
  { $limit: 10 }
])

Note: The initial $match with $type: "date" ensures that only documents with a valid released field are processed. Result: more than 1,000 films in 2014, similar figures in 2013 and 2012.

Example 4 — Create year-month labels with $dateToString

Produce keys of type YYYY-MM for monthly analysis:

db.movies.aggregate([
  { $group: {
      _id: { ym: { $dateToString: { format: "%Y-%m", date: "$released" } } },
      count: { $sum: 1 }
  }},
  { $project: { _id: 0, yearMonth: "$_id.ym", count: 1 } },
  { $sort: { yearMonth: -1 } },
  { $limit: 12 }
])

Result: Readable pairs like 2016-03 with 3 films or 2015-04 with 97 films.

Demo: Group by time period

This demo shows how MongoDB can group movies by different time periods: by year, by month in a given year, and by decade.

Example 1 — Summary by YEAR with $dateTrunc

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $addFields: { yearStart: { $dateTrunc: { date: "$released", unit: "year" } } } },
  { $group: { _id: "$yearStart", titles: { $sum: 1 } } },
  { $sort: { _id: 1 } },
  { $limit: 15 }
])

Result: Early years like 1903, 1909, 1911 have only a few titles, with the number gradually increasing in later years.

Example 2 — Summary by MONTH for a specific year

Count monthly outputs for the year 2010:

db.movies.aggregate([
  { $match: {
      released: {
        $gte: ISODate("2010-01-01"),
        $lt:  ISODate("2011-01-01")
      }
  }},
  { $addFields: { monthStart: { $dateTrunc: { date: "$released", unit: "month" } } } },
  { $group: { _id: "$monthStart", titles: { $sum: 1 } } },
  { $sort: { _id: 1 } }
])

Result: February with 49 releases, April with 90, November with 77 — a detailed portrait of activity this year.

Example 3 — Summary by DECADE (10 year increments)

$dateTrunc does not have a “decade” unit, so it must be calculated manually:

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $addFields: {
      releaseYear: { $year: "$released" },
      decadeStartYear: {
        $multiply: [
          { $floor: { $divide: [ { $year: "$released" }, 10 ] } },
          10
        ]
      }
  }},
  { $group: { _id: "$decadeStartYear", titles: { $sum: 1 } } },
  { $sort: { _id: 1 } }
])

Logic: We extract the year, divide by 10, take the floor, then multiply by 10. This gives values ​​like 1970, 1980, 1990.

Result: A few releases at the beginning of the 20th century, hundreds in the middle of the century, then thousands in recent decades (more than 5,000 titles in the 2010s).

Demo: Date calculations with $subtract and $dateDiff

This demo explores how MongoDB calculates the differences between dates — useful for measuring the age of a record or tracking time gaps between events.

Example 1 — Number of days since release ($$NOW vs released)

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      released: 1,
      daysSinceRelease: {
        $dateDiff: { startDate: "$released", endDate: "$$NOW", unit: "day" }
      }
  }},
  { $limit: 5 }
])

Note: $$NOW is a system variable representing the current time. The Great Train Robbery is over 44,000 days old — over a century.

Example 2 — Delay between output and last update (string → date conversion)

Many documents store lastupdated as a string. You must first convert it:

db.movies.aggregate([
  { $match: { released: { $type: "date" }, lastupdated: { $exists: true } } },
  { $addFields: { lastUpdatedDate: { $toDate: "$lastupdated" } } },
  { $match: { lastUpdatedDate: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      released: 1,
      lastUpdatedDate: 1,
      daysBetween: {
        $dateDiff: { startDate: "$released", endDate: "$lastUpdatedDate", unit: "day" }
      }
  }},
  { $limit: 5 }
])

Example 3 — Using $subtract (in milliseconds) and converting to days

Low level approach — produce the same result with direct subtraction:

db.movies.aggregate([
  { $match: { released: { $type: "date" }, lastupdated: { $exists: true } } },
  { $addFields: { lastUpdatedDate: { $toDate: "$lastupdated" } } },
  { $match: { lastUpdatedDate: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      released: 1,
      lastUpdatedDate: 1,
      daysBetween: {
        $divide: [
          { $subtract: [ "$lastUpdatedDate", "$released" ] },
          1000 * 60 * 60 * 24  // ms -> jours
        ]
      }
  }},
  { $limit: 5 }
])

Note: Subtracting two dates produces a result in milliseconds. MongoDB supports both high-level ($dateDiff) and low-level ($subtract) approaches.

Example 4 — Number of months since last update

db.movies.aggregate([
  { $match: { lastupdated: { $exists: true } } },
  { $addFields: { lastUpdatedDate: { $toDate: "$lastupdated" } } },
  { $match: { lastUpdatedDate: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      lastUpdatedDate: 1,
      monthsSinceUpdate: {
        $dateDiff: { startDate: "$lastUpdatedDate", endDate: "$$NOW", unit: "month" }
      }
  }},
  { $limit: 5 }
])

Result: Some movies show about 120 months since last update, or about 10 years — easy to interpret to assess data freshness.

Demo: Format dates for reporting

This demo transforms raw dates into clean strings for reports, dashboards and BI tools.

Example 1 — Format the release date as YYYY-MM-DD

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      releaseDate: {
        $dateToString: { format: "%Y-%m-%d", date: "$released" }
      }
  }},
  { $limit: 5 }
])

Result: 1903-12-01, 1909-12-13, etc. — predictable and standardized.

Example 2 — Format with abbreviated month name

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      releaseDate: {
        $dateToString: { format: "%b %Y", date: "$released" }
      }
  }},
  { $limit: 5 }
])

Result: December 1903, December 1909, April 1911 — friendly for chart labels.

Example 3 — Add a time zone to formatting

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $project: {
      _id: 0,
      title: 1,
      releaseDateNY: {
        $dateToString: {
          format: "%Y-%m-%d %H:%M:%S",
          date: "$released",
          timezone: "America/New_York"
        }
      }
  }},
  { $limit: 5 }
])

Note: The same instant is rendered in the specified time zone. The evening hours appear because the stored instant is rendered with the offset of the America/New_York zone.

Example 4 — Year-month keys for BI reporting

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $group: {
      _id: { ym: { $dateToString: { format: "%Y-%m", date: "$released" } } },
      count: { $sum: 1 }
  }},
  { $project: { _id: 0, yearMonth: "$_id.ym", count: 1 } },
  { $sort: { yearMonth: 1 } },
  { $limit: 12 }
])

Result: Keys of type 1903-12, 1909-12 with their respective accounts — ready to be attached or viewed in BI tools.

Query patterns for dates

Four common query patterns are worth remembering:

Dates in MongoDB are not just for storage. They calculate, group and format so your analysis is always on time. Whether you’re reporting to auditors, tracking user activity, or investigating long-term patterns, MongoDB’s date operators allow you to shape your data into accurate, current stories.

3. Querying and Analyzing Data Stored in Arrays

Fundamental concepts of arrays

Arrays are one of the most flexible features of MongoDB. Unlike rigid relational tables, MongoDB arrays are dynamic and can expand as needed.

Key Features:

• A single array can contain tens of thousands of elements.
• Queries can easily match any element in the array without an index position — making filters simpler.
• Arrays can store subdocuments with their own nested fields.
• arrays within arrays are possible with deep nesting levels.

Demo: Matching values ​​in an array with $in

Example 1 — Films matching multiple genres

Find films that are in the Drama or Comedy genre:

use sample_mflix

db.movies.find(
  { genres: { $in: ["Drama", "Comedy"] } },
  { title: 1, genres: 1, _id: 0 }
).limit(5)

Explanation: The $in operator checks each element of the array and compares it to a list of values. If an element matches, the document is returned. No need to specify a position like genders at position 1 or 2.

Result: Includes A Corner in Wheat and Gertie the Dinosaur, each with multiple genres.

Example 2 — Negation with $nin

Exclude Action or Horror movies:

db.movies.find(
  { genres: { $nin: ["Action", "Horror"] } },
  { title: 1, genres: 1, _id: 0 }
).limit(5)

Example 3 — $in numeric

Find films released in 1999, 2005 or 2010:

db.movies.find(
  { year: { $in: [1999, 2005, 2010] } },
  { title: 1, year: 1, _id: 0 }
).limit(5)

Result: Movies like Toy Story 2 and Three Kings.

Example 4 — Regex with $in

Regular expressions work with $in for pattern matching:

db.movies.find(
  { cast: { $in: [/^Harry/, /^Will/] } },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Result: Movies with actors whose last name starts with “Harry” or “Will”.

Example 5 — Logical AND with $in

Find films in English that are also Comedy or Drama:

db.movies.find(
  { languages: "English", genres: { $in: ["Comedy", "Drama"] } },
  { title: 1, languages: 1, genres: 1, _id: 0 }
).limit(5)

Explanation: The condition on languages works in conjunction with the $in filter on genres.

Demo: Filtering arrays with $elemMatch

$elemMatch is designed to apply multiple rules to the same element of an array, rather than satisfying each rule with different elements.

Example 1 — Basic use of $elemMatch

Find films where a cast member is exactly “Tom Hanks”:

db.movies.find(
  { cast: { $elemMatch: { $eq: "Tom Hanks" } } },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Result: A neat list of classics starring Tom Hanks — Splash, Big, etc.

Example 2 — Two rules on the SAME cast element

Names starting with “Tom” AND containing an “r”:

db.movies.find(
  { cast: { $elemMatch: { $regex: /^Tom.*r/i } } },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Why use $elemMatch? Without it, MongoDB could satisfy one rule with one cast member and the other rule with another — which is not what we want.

Example 3 — Inclusion and exclusion in a single element

Cast that starts with “Tom” but is NOT exactly “Tom Hanks”:

db.movies.find(
  { cast: { $elemMatch: { $regex: /^Tom/i, $nin: ["Tom Hanks"] } } },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Example 4 — Numerical test on an array built in the pipeline

Combine IMDb and Tomatoes ratings, keep films with at least a rating ≥ 8:

db.movies.aggregate([
  { $addFields: { ratings: ["$imdb.rating", "$tomatoes.viewer.rating"] } },
  { $match: { ratings: { $elemMatch: { $gte: 8 } } } },
  { $project: { _id: 0, title: 1, ratings: 1 } },
  { $limit: 5 }
])

Result: Movies like City Lights and The Music Box, each with at least one strong rating.

Demo: Filter by array length with $size

Sometimes the important question is not what values ​​are in an array, but how many there are.

Example 1 — Exact length with $size

Find films with exactly 3 languages:

db.movies.find(
  { languages: { $size: 3 } },
  { title: 1, languages: 1, _id: 0 }
).limit(5)

Result: In Old Arizona and The Man Who Knew Too Much, each with exactly three languages.

Example 2 — Length comparison with $expr (safe for missing fields)

Find films with at least 3 members in the cast:

db.movies.find(
  { $expr: { $gte: [ { $size: { $ifNull: ["$cast", []] } }, 3 ] } },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Note: $ifNull returns an empty array if cast is missing or is not an array. This avoids errors on incomplete documents.

Example 3 — Length range with $expr

Find movies with between 2 and 4 genres included:

db.movies.find(
  { $expr: { $and: [
      { $gte: [ { $size: { $ifNull: ["$genres", []] } }, 2 ] },
      { $lte: [ { $size: { $ifNull: ["$genres", []] } }, 4 ] }
  ] } },
  { title: 1, genres: 1, _id: 0 }
).limit(5)

Result: A Corner in Wheat and Traffic in Souls, each with between 2 and 4 genres.

Example 4 — Count only valid ratings in the pipeline

Construct a filtered rating array (without null or NaN) and only keep films with at least 1 rating:

db.movies.aggregate([
  {
    $project: {
      _id: 0,
      title: 1,
      ratings: {
        $filter: {
          input: [ "$imdb.rating", "$tomatoes.viewer.rating" ],
          as: "r",
          cond: { $and: [ { $ne: ["$$r", null] }, { $ne: ["$$r", NaN] } ] }
        }
      }
    }
  },
  { $match: { $expr: { $gte: [ { $size: "$ratings" }, 1 ] } } },
  { $limit: 5 }
])

Demo: Unwinding arrays in aggregation with $unwind

$unwind takes an array field and splits it so that each element gets its own document in the pipeline.

Example 0 — Preview before $unwind

db.movies.find(
  {},
  { title: 1, genres: 1, _id: 0 }
).limit(5)

Each film shows genres as a single array: The Great Train Robbery with [“Short”, “Western”].

Example 1 — Basic $unwind on genres

db.movies.aggregate([
  { $unwind: "$genres" },
  { $project: { _id: 0, title: 1, genre: "$genres" } },
  { $limit: 10 }
])

Result: Instead of a document with multiple genres, each genre gets its own row. The Great Train Robbery becomes two lines, one for “Short” and one for “Western”. This doubling is not a bug — it is exactly what allows each genre to be analyzed independently.

Example 2 — Count movies by genre

db.movies.aggregate([
  { $unwind: "$genres" },
  { $group: { _id: "$genres", movieCount: { $sum: 1 } } },
  { $sort: { movieCount: -1, _id: 1 } },
  { $limit: 10 }
])

Result: Drama and Comedy dominate with tens of thousands of titles, followed by Romance, Crime and Thriller.

Example 3 — Average rating by genre

db.movies.aggregate([
  { $unwind: "$genres" },
  { $group: {
      _id: "$genres",
      avgRating: { $avg: "$imdb.rating" },
      titles: { $sum: 1 }
  } },
  { $project: { _id: 0, genre: "$_id", titles: 1, avgRating: { $round: ["$avgRating", 2] } } },
  { $sort: { avgRating: -1, titles: -1 } },
  { $limit: 10 }
])

Result: Film-Noir and Short display high averages.

Example 4 — Gender trend by year

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $unwind: "$genres" },
  { $project: { _id: 0, genre: "$genres", year: { $year: "$released" } } },
  { $group: { _id: { genre: "$genre", year: "$year" }, movieCount: { $sum: 1 } } },
  { $sort: { "_id.genre": 1, "_id.year": 1 } },
  { $limit: 20 }
])

Note: Initial $match with $type: "date" avoids errors with the $year operator.

Demo: Combine $unwind and $group for analysis

More complex analytical questions can be solved by combining several aggregation stages.

Teaching note: You do not need to memorize every syntax here. The goal is to see what MongoDB can do with arrays. In real life, you can always come back and check the exact syntax.

Example 1 — Top global actors (denuplicated per movie)

Who appears in the most titles in the entire catalog?

db.movies.aggregate([
  { $unwind: "$cast" },
  { $group: { _id: "$cast", movieIds: { $addToSet: "$_id" } } },
  { $project: { _id: 0, actor: "$_id", movieCount: { $size: "$movieIds" } } },
  { $sort: { movieCount: -1, actor: 1 } },
  { $limit: 10 }
])

Note: $addToSet collects unique movie IDs to ensure that the same movie is not counted twice.

Result: Names like Gerard, Robert De Niro and Michael Caine, each with dozens of films.

Example 2 — Share of each gender by decade

In each decade, what share does each genre represent?

db.movies.aggregate([
  { $match: { released: { $type: "date" } } },
  { $project: {
      _id: 0,
      genres: 1,
      decade: { $multiply: [ { $floor: { $divide: [ { $year: "$released" }, 10 ] } }, 10 ] }
  }},
  { $unwind: "$genres" },
  { $group: { _id: { decade: "$decade", genre: "$genres" }, count: { $sum: 1 } } },
  { $group: {
      _id: "$_id.decade",
      total: { $sum: "$count" },
      byGenre: { $push: { genre: "$_id.genre", count: "$count" } }
  }},
  { $unwind: "$byGenre" },
  { $project: {
      _id: 0,
      decade: "$_id",
      genre: "$byGenre.genre",
      count: "$byGenre.count",
      sharePct: { $round: [ { $multiply: [ { $divide: ["$byGenre.count", "$total"] }, 100 ] }, 2 ] }
  }},
  { $sort: { decade: 1, sharePct: -1, genre: 1 } },
  { $limit: 40 }
])

Result: Drama and Comedy dominated the first decades, while Action, Adventure and Animation gained shares later.

Example 3 — Pairs of actors that often appear together

Who frequently appears together in films?

db.movies.aggregate([
  { $match: { cast: { $type: "array" } } },
  { $project: {
      title: 1,
      castLimited: {
        $filter: {
          input: { $slice: ["$cast", 5] },
          as: "c",
          cond: { $ne: ["$$c", null] }
        }
      }
  }},
  { $project: { title: 1, castA: "$castLimited", castB: "$castLimited" } },
  { $unwind: { path: "$castA", includeArrayIndex: "i" } },
  { $unwind: { path: "$castB", includeArrayIndex: "j" } },
  { $match: { $expr: { $lt: ["$i", "$j"] } } },
  { $group: { _id: { a1: "$castA", a2: "$castB" }, together: { $sum: 1 } } },
  { $sort: { together: -1, "_id.a1": 1, "_id.a2": 1 } },
  { $limit: 15 },
  { $project: { _id: 0, actor1: "$_id.a1", actor2: "$_id.a2", moviesTogether: "$together" } }
])

Logic: We limit the cast to the first 5 members, we duplicate the array, we unfold the two copies and we filter to keep only the pairs where the index i < j (avoiding duplicates in reverse order).

Result: Max and Liv worked together in 12 films, Max and Eddie in 10 films.

Query patterns for arrays

Four patterns come up constantly when working with arrays in MongoDB:

Arrays in MongoDB are not simple lists. They adapt, interlock and reveal insights at each level. Whether you’re filtering a single match, summarizing for a report, or analyzing deeper relationships, arrays provide both flexibility and analytical power.

4. Accessing and Reshaping Nested Fields

Basic concepts about nested fields

Nested fields are one of MongoDB’s greatest strengths. Instead of spreading data across many tables, you can store subdocuments and arrays directly inside a document.

Important points:

• Nested fields in MongoDB can be up to 100 levels deep.
• A simple nested path can include dots, arrays and subdocuments all mixed together.
• Warning: Queries fail silently if a nested path is misspelled — precision is essential.
• When the hierarchy becomes too deep, the $project stage can flatten nested values ​​into top-level fields, providing a clean view for analysis.

Demo: Query nested fields with dot notation

Example 1 — Filter on nested awards field

Find films with more than 5 awards:

use sample_mflix

db.movies.find(
  { "awards.wins": { $gt: 5 } },
  { title: 1, "awards.wins": 1, _id: 0 }
).limit(5)

Explanation: awards.wins uses dot notation to access the awards subdocument and its wins field. The first dot tells MongoDB to navigate the document hierarchy.

Result: Films like Morocco and The Informer, each with more than 5 awards.

Example 2 — Matching by nested rating viewer

Find films with a viewer rating ≥ 4:

db.movies.find(
  { "tomatoes.viewer.rating": { $gte: 4 } },
  { title: 1, "tomatoes.viewer.rating": 1, _id: 0 }
).limit(5)

Note: tomatoes.viewer.rating uses a colon. The first enters the viewer subdocument inside tomatoes, and the second accesses the rating field.

Result: The Italian and Napoleon, both with a viewer rating ≥ 4.

Example 3 — Combine conditions across multiple nested paths

Films with critic rating ≥ 3 AND viewer rating ≥ 4:

db.movies.find(
  {
    "tomatoes.critic.rating": { $gte: 3 },
    "tomatoes.viewer.rating": { $gte: 4 }
  },
  {
    title: 1,
    "tomatoes.critic.rating": 1,
    "tomatoes.viewer.rating": 1,
    _id: 0
  }
).limit(5)

Result: City Lights and Trouble in Paradise meet both criteria.

Example 4 — Dot notation with arrays (access by index)

Find films where Tom Hanks is the first cast member:

db.movies.find(
  { "cast.0": "Tom Hanks" },
  { title: 1, cast: 1, _id: 0 }
).limit(5)

Explanation: cast.0 with a number acts as an index and points directly to the first element of the array. This allows you to check if Tom Hanks is headlining.

Result: Splash and Big — Tom Hanks is indeed the first actor listed.

Example 5 — Combination: dot notation + array logic

High viewer rating (≥ 4.3) AND at least 3 members in the cast:

db.movies.find(
  {
    "tomatoes.viewer.rating": { $gte: 4.3 },
    $expr: { $gte: [ { $size: "$cast" }, 3 ] }
  },
  { title: 1, cast: { $slice: ["$cast", 3] }, "tomatoes.viewer.rating": 1, _id: 0 }
).limit(5)

Result: Classics like Napoleon and City Lights — strong rating and sufficiently large cast.

Demo: Project and flatten nested values

Nested fields are not just for filtering. You can reorganize them into cleaner top-level fields.

Example 1 — Flatten a nested address

Show theater name and city as top level field:

db.theaters.find(
  {},
  {
    _id: 0,
    name: 1,
    city: "$location.address.city"
  }
).limit(5)

Result: Theaters with their names and cities (Bloomington, California, Birmingham).

Example 2 — Extract multiple nested values ​​simultaneously

Flatten both city and state from theater address:

db.theaters.find(
  {},
  {
    _id: 0,
    name: 1,
    city: "$location.address.city",
    state: "$location.address.state"
  }
).limit(5)

Result: City-state pairs like Bloomington (Minnesota), Vacaville (California), Tempe (Arizona).

Example 3 — Flatten nested array objects with join

From the comments collection, show the name of the commentator and the title of the corresponding film:

db.comments.aggregate([
  {
    $lookup: {
      from: "movies",
      localField: "movie_id",
      foreignField: "_id",
      as: "movie"
    }
  },
  { $unwind: "$movie" },
  {
    $project: {
      _id: 0,
      commenter: "$name",
      movieTitle: "$movie.title"
    }
  },
  { $limit: 5 }
])

Note: $lookup creates an array, $unwind is necessary to align each comment with exactly one movie.

Example 4 — Raw style vs. flattened style in the same row

Compare the two display styles with $objectToArray:

db.movies.aggregate([
  { $project: {
      _id: 0,
      title: 1,
      tomatoes_kv: { $objectToArray: "$tomatoes" }
  }},
  { $unwind: "$tomatoes_kv" },
  { $project: {
      title: 1,
      metric: "$tomatoes_kv.k",
      rawValue: "$tomatoes_kv.v",
      rating: "$tomatoes_kv.v.rating",
      numReviews: "$tomatoes_kv.v.numReviews",
      meter: "$tomatoes_kv.v.meter"
  }},
  { $limit: 8 }
])

Explanation of results:

• When metric is fresh, rawValue is a simple number (eg: 6 for The Great Train Robbery). MongoDB displays it as is.
• When metric is critic, rawValue is a nested object containing rating, numReviews and meter. MongoDB shows both perspectives in the same row: the preserved raw object and the flattened fields.
• When metric is rotten or lastupdated, it is still a simple number or date — displayed as is.

Demo: Combine $match and $project on nested fields

Example 1 — Filter by nested rating viewer, project flattened score

db.movies.aggregate([
  { $match: { "tomatoes.viewer.rating": { $gte: 4 } } },
  { $project: {
      _id: 0,
      title: 1,
      viewerScore: "$tomatoes.viewer.rating"
  }},
  { $limit: 5 }
])

Result: The Italian and Napoleon — their viewer score is displayed directly on the first level.

Example 2 — Mix nested filters and first level filters

Popular movies (imdb.votes ≥ 100,000) with a good critical score (≥ 3):

db.movies.aggregate([
  { $match: {
      "imdb.votes": { $gte: 100000 },
      "tomatoes.critic.rating": { $gte: 3 }
  }},
  { $project: {
      _id: 0,
      title: 1,
      votes: "$imdb.votes",
      criticScore: "$tomatoes.critic.rating"
  }},
  { $limit: 5 }
])

Result: Classics like Modern Times and Citizen Kane with their votes and critical scores in their own columns.

Example 3 — Nested geo match + flattening

Theaters in New York with valid coordinates, showing flattened latitude/longitude:

db.theaters.aggregate([
  { $match: {
      "location.address.state": "NY",
      "location.geo.type": "Point",
      "location.geo.coordinates.0": { $type: "number" },
      "location.geo.coordinates.1": { $type: "number" }
  }},
  { $project: {
      _id: 0,
      name: 1,
      city:  "$location.address.city",
      state: "$location.address.state",
      longitude: { $arrayElemAt: ["$location.geo.coordinates", 0] },
      latitude:  { $arrayElemAt: ["$location.geo.coordinates", 1] }
  }},
  { $limit: 5 }
])

Note: $arrayElemAt extracts a specific element from an array by its index — here the geographic coordinates.

Result: Theaters in cities like New York City, Mohegan Lake, and Elmira, with their latitudes and longitudes on the first level.

Demo: Troubleshooting nesting inconsistencies

The goal of this sequence is to audit the quality of the date fields in the movies collection, identify inconsistencies, and understand how to troubleshoot them.

Example 0 — Quick view of records

db.movies.find(
  {},
  { _id: 0, title: 1, year: 1, released: 1 }
).limit(5)

Result: The Great Train Robbery (1903), A Corner in Wheat (1909) — mental models for the future.

Example 1 — Type census

Capture the actual type stored in the released and year fields:

db.movies.aggregate([
  { $project: {
      _id: 0,
      releasedType: { $type: "$released" },
      yearType:     { $type: "$year" }
  }},
  { $group: {
      _id: { releasedType: "$releasedType", yearType: "$yearType" },
      count: { $sum: 1 }
  }},
  { $sort: { count: -1 } }
])

Results:

• ≈ 20,845 documents: released is a date and year is an integer — exactly what we want.
• 469 documents: released is missing, but year is an integer — incomplete.
• 33 documents: year is a string, while released is a valid date — risky.
• 2 documents: year is a string AND released is missing — very risky.

Example 2 — Quantifying inconsistent patterns

db.movies.aggregate([
  { $project: {
      releasedType: { $type: "$released" },
      yearType:     { $type: "$year" },
      yearNum:      { $convert: { input: "$year", to: "int", onError: null, onNull: null } },
      releasedYear: {
        $cond: [ { $eq: [ { $type: "$released" }, "date" ] }, { $year: "$released" }, null ]
      }
  }},
  { $group: {
      _id: null,
      total:           { $sum: 1 },
      missingReleased: { $sum: { $cond: [ { $eq: [ "$releasedType", "missing" ] }, 1, 0 ] } },
      yearIsString:    { $sum: { $cond: [ { $eq: [ "$yearType", "string" ] }, 1, 0 ] } },
      yearMismatch:    { $sum: { $cond: [
                             { $and: [
                               { $ne: [ "$yearNum", null ] },
                               { $ne: [ "$releasedYear", null ] },
                               { $ne: [ "$yearNum", "$releasedYear" ] }
                             ]},
                             1, 0
                           ] } }
  }}
])

Results: Out of 21,349 total documents:

• 471 have released missing
• 35 have year stored as string
• 5,044 have a yearMismatch (the numeric year does not match the year of the release date) — the biggest problem.

Example 3 — Real-world examples of mismatched lines

db.movies.aggregate([
  { $project: {
      _id: 0,
      title: 1,
      yearNum: { $convert: { input: "$year", to: "int", onError: null, onNull: null } },
      releasedDate: {
        $cond: [ { $eq: [ { $type: "$released" }, "date" ] }, "$released", null ]
      }
  }},
  { $addFields: {
      releasedYear: {
        $cond: [ { $ne: [ "$releasedDate", null ] }, { $year: "$releasedDate" }, null ]
      }
  }},
  { $match: { $expr: {
      $and: [
        { $ne: [ "$yearNum", null ] },
        { $ne: [ "$releasedYear", null ] },
        { $ne: [ "$yearNum", "$releasedYear" ] }
      ]
  }}},
  { $project: {
      title: 1,
      year: "$yearNum",
      released: { $dateToString: { date: "$releasedDate", format: "%Y-%m-%d" } },
      releasedYear: 1
  }},
  { $limit: 5 }
])

Examples of results:

• The Ace of Hearts: year = 1921 but released = 1924-04-04
• The Four Horsemen of the Apocalypse: year = 1921 but released = 1923-03-31

These delays may be due to films shot one year but released later, or simply to human errors.

Example 4 — Group offsets by bucket

db.movies.aggregate([
  { $project: {
      yearNum: { $convert: { input: "$year", to: "int", onError: null, onNull: null } },
      releasedYear: {
        $cond: [ { $eq: [ { $type: "$released" }, "date" ] }, { $year: "$released" }, null ]
      }
  }},
  { $match: { $expr: {
      $and: [
        { $ne: [ "$yearNum", null ] },
        { $ne: [ "$releasedYear", null ] },
        { $ne: [ "$yearNum", "$releasedYear" ] }
      ]
  }}},
  { $project: { yearOffset: { $subtract: [ "$yearNum", "$releasedYear" ] } } },
  { $group: { _id: "$yearOffset", count: { $sum: 1 } } },
  { $project: { _id: 0, yearOffset: "$_id", count: 1 } },
  { $sort: { count: -1, yearOffset: 1 } },
  { $limit: 20 }
])

Results:

• Strong peak at -1 with almost 3,918 documents — the year is one year behind the release date.
• ≈ 669 documents with a gap of 2 years.
• ≈ 173 with a gap of 3 years, ≈ 74 with a gap of 4 years.
• Some films have a gap of several decades — clearly input errors.

Interpretation: The majority of gaps are only one or two years, which is consistent if one field represents the year of production and the other the year of release. Decades-long gaps are clear errors that need to be corrected.

Query patterns for nested structures

Nested fields in MongoDB aren’t just details — they’re treasure maps pointing to deeper insights. With the right combination of flattening, schema tolerance, denormalization, and output shaping, you can unlock this treasure and make your analysis both powerful and practical.

5. Solving Real world Query Challenges as an Analyst

Real Analysis Module Overview

This module is where MongoDB’s aggregation framework really shines. Instead of being limited to flat filters, you can join collections, calculate new values ​​on the fly, and shape outputs for reporting.

Interesting facts about pipeline aggregation:

• A MongoDB aggregation pipeline can include up to 100 stages — chaining it step by step without hitting a cap too early.
• $unwind can take a single document with an embedded array and explode it into thousands of individual outputs.
• Pipelines have the versatility to handle both real-time queries and batch reporting.
• $merge allows writing results to a collection — transforming your aggregation from a read-only operation into a workflow that can update or create datasets for downstream use.

The MongoDB aggregation framework is much more than a query tool. It’s a miniature data processing engine that allows you to build repeatable and scalable workflows.

Demo: Date filters combined with user attributes

Example 1 — Date range + user attribute (email domain)

Comments from 2014 to 2015 by Gmail users:

use sample_mflix

db.comments.aggregate([
  { $match: {
      date: { $gte: ISODate("2014-01-01"), $lt: ISODate("2016-01-01") }
  }},
  { $lookup: {
      from: "users",
      localField: "email",
      foreignField: "email",
      as: "user"
  }},
  { $unwind: "$user" },
  { $match: { "user.email": /gmail\.com$/i } },
  { $project: {
      _id: 0,
      date: 1,
      commenter: "$name",
      email: "$user.email",
      text: 1
  }},
  { $limit: 5 }
])

Pipeline architecture:

1. $match → filter by date window
2. $lookup → enrich with user details
3. $unwind → flatten the array created by $lookup
4. $match → filter only Gmail addresses by regex
5. $project → own output

Example 2 — Date range + calculated “VIP” tag (frequent commenters)

In 2015, only keep comments from users with ≥ 20 total comments:

db.comments.aggregate([
  { $match: {
      date: { $gte: ISODate("2015-01-01"), $lt: ISODate("2016-01-01") }
  }},
  { $lookup: {
      from: "comments",
      let: { me: "$email" },
      pipeline: [
        { $match: { $expr: { $eq: ["$email", "$$me"] } } },
        { $count: "n" }
      ],
      as: "counts"
  }},
  { $addFields: { totalComments: { $ifNull: [ { $arrayElemAt: ["$counts.n", 0] }, 0 ] } } },
  { $match: { totalComments: { $gte: 20 } } },
  { $project: {
      _id: 0,
      date: 1,
      commenter: { $ifNull: ["$user.name", "$name"] },
      email: 1,
      totalComments: 1,
      text: 1
  }},
  { $limit: 5 }
])

Key note: This $lookup uses a sub-pipeline — it runs a nested query to count the total number of comments from each user, on the fly.

Result: Names like Gregor and Lannister with hundreds of lifetime posts — clearly in the VIP category.

Demo: Aggregate values ​​from arrays

Example 1 — Top actors by genre (window functions with $topN)

For each genre, list the 5 most frequent actors:

db.movies.aggregate([
  { $match: { cast: { $type: "array" }, genres: { $type: "array" } } },
  { $unwind: "$genres" },
  { $unwind: "$cast" },
  { $group: { _id: { genre: "$genres", actor: "$cast" }, appearances: { $sum: 1 } } },
  { $group: {
      _id: "$_id.genre",
      topActors: {
        $topN: {
          n: 5,
          sortBy: { appearances: -1 },
          output: { actor: "$_id.actor", appearances: "$appearances" }
        }
      }
  }},
  { $project: { _id: 0, genre: "$_id", topActors: 1 } },
  { $sort: { genre: 1 } }
])

Result: Gerard dominates the romance, Arnold Schwarzenegger often appears in sci-fi, Jackie Chan leads the action with dozens of films.

Example 2 — Genre Exclusivity Index

For each genre, count solo vs. mixed appearances and calculate exclusivity: exclusivity = soloAppearances / totalAppearances

db.movies.aggregate([
  { $match: { genres: { $type: "array", $ne: [] } } },
  { $project: {
      genresSet: { $setUnion: ["$genres", []] },
      gcount:    { $size: { $setUnion: ["$genres", []] } }
  }},
  { $unwind: "$genresSet" },
  { $group: {
      _id: "$genresSet",
      movies: { $sum: 1 },
      solo:   { $sum: { $cond: [ { $eq: ["$gcount", 1] }, 1, 0 ] } },
      mixed:  { $sum: { $cond: [ { $gt: ["$gcount", 1] }, 1, 0 ] } }
  }},
  { $project: {
      _id: 0,
      genre: "$_id",
      movies: 1,
      solo: 1,
      mixed: 1,
      exclusivity: { $round: [ { $divide: ["$solo", "$movies"] }, 2 ] }
  }},
  { $sort: { exclusivity: -1, movies: -1, genre: 1 } },
  { $limit: 15 }
])

Note: $setUnion is used to deduplicate genres per movie. $cond counts conditional occurrences.

Result: Documentaries come out on top with almost half of their appearances being solo. Romance and Crime fall at the bottom with a very low exclusivity score.

Demo: Group activity by time intervals

Example 1 — Comments per month (YYYY-MM format)

Count comments per calendar month from 2014 to 2016:

db.comments.aggregate([
  { $match: { date: { $gte: ISODate("2014-01-01"), $lt: ISODate("2017-01-01") } } },
  { $group: {
      _id: { $dateTrunc: { date: "$date", unit: "month" } },
      comments: { $sum: 1 }
  }},
  { $project: {
      _id: 0,
      month: { $dateToString: { date: "$_id", format: "%Y-%m" } },
      comments: 1
  }},
  { $sort: { month: 1 } },
  { $limit: 36 }
])

Result: Clean pairs like 2014-01 with 94 comments, 2014-05 with 90 — ready to plug directly into a chart or timeline.

Example 2 — Daily trend with 7-day moving average and cumulative total

db.comments.aggregate([
  { $match: { date: { $gte: ISODate("2015-01-01"), $lt: ISODate("2016-01-01") } } },
  { $group: {
      _id: { $dateTrunc: { date: "$date", unit: "day" } },
      count: { $sum: 1 }
  }},
  { $project: { _id: 0, day: "$_id", count: 1 } },
  { $sort: { day: 1 } },
  { $setWindowFields: {
      sortBy: { day: 1 },
      output: {
        movingAvg7d: { $avg: "$count", window: { documents: [ -6, 0 ] } },
        cumulative:  { $sum: "$count", window: { documents: [ "unbounded", 0 ] } }
      }
  }},
  { $project: {
      day: { $dateToString: { date: "$day", format: "%Y-%m-%d" } },
      count: 1,
      movingAvg7d: { $round: ["$movingAvg7d", 2] },
      cumulative: 1
  }},
  { $limit: 90 }
])

Explanation of $setWindowFields:

• movingAvg7d: moving average over the previous 7 days (documents: [-6, 0])
• cumulative: sum of all comments since the beginning (documents: ["unbounded", 0])

Result: Rows like January 1, 2015 with 4 comments, a 7-day average of 4, and a cumulative total of 4 — gradually growing day by day.

Demo: Flatten nested structures for reporting

Example 1 — One line per actor with ratings

Flatten the films so that each line represents a pair {title, actor, viewerScore, criticScore}:

db.movies.aggregate([
  { $match: { cast: { $type: "array", $ne: [] } } },
  { $unwind: "$cast" },
  { $project: {
      _id: 0,
      title: 1,
      year: 1,
      actor: "$cast",
      viewerScore: "$tomatoes.viewer.rating",
      criticScore: "$tomatoes.critic.rating"
  }},
  { $limit: 10 }
])

Result: A Corner in Wheat is no longer a single record, but several lines — one per actor. Each line has the same year and the same viewer score. Extremely useful for analysis where actors are the unit of study.

Example 2 — Flat comments report enriched with information from the film

For each comment, output: {date, commentator, email, movieTitle, year, genresCsv}:

db.comments.aggregate([
  { $lookup: {
      from: "movies",
      localField: "movie_id",
      foreignField: "_id",
      as: "movie"
  }},
  { $unwind: "$movie" },
  { $project: {
      _id: 0,
      date: { $dateToString: { date: "$date", format: "%Y-%m-%d" } },
      commenter: "$name",
      email: "$email",
      movieTitle: "$movie.title",
      year: "$movie.year",
      genresCsv: {
        $reduce: {
          input: { $ifNull: ["$movie.genres", []] },
          initialValue: "",
          in: {
            $concat: [
              { $cond: [{ $eq: ["$$value", ""] }, "", { $concat: ["$$value", ", "] }] },
              "$$this"
            ]
          }
        }
      }
  }},
  { $limit: 10 }
])

Note: $reduce concatenates the genre array into a single CSV string. The results show not only who commented and when, but also which film was affected and its genres in a clean format like drama, romance.

Demo: Format data for external use

When data is exported to spreadsheets or CSVs, the goal is always the same: clean, consistent, and ready for downstream analysis.

Example 1 — Compact export of films (date + genres CSV)

db.movies.aggregate([
  { $project: {
      _id: 0,
      title: 1,
      releaseDate: {
        $cond: [
          { $eq: [ { $type: "$released" }, "date" ] },
          { $dateToString: { date: "$released", format: "%Y-%m-%d" } },
          null
        ]
      },
      genresCsv: {
        $reduce: {
          input: { $ifNull: ["$genres", []] },
          initialValue: "",
          in: { $concat: [
            { $cond: [ { $eq: ["$$value", ""] }, "", { $concat: ["$$value", ", "] } ] },
            "$$this"
          ] }
        }
      }
  }},
  { $limit: 5 }
])

Note: $cond checks if released is really a date — if not, returns null to keep things predictable.

Result: The Great Train Robbery → date 1903-12-01, genres Short, Western. Instantly recognizable to anyone working with Excel or BI tools.

Example 2 — Compact export of comments with film info

db.comments.aggregate([
  { $lookup: { from: "movies", localField: "movie_id", foreignField: "_id", as: "m" } },
  { $unwind: "$m" },
  { $project: {
      _id: 0,
      commentTime: { $dateToString: { date: "$date", format: "%Y-%m-%d %H:%M" } },
      commenter: "$name",
      movieTitle: "$m.title",
      genresCsv: {
        $reduce: {
          input: { $ifNull: ["$m.genres", []] },
          initialValue: "",
          in: { $concat: [
            { $cond: [ { $eq: ["$$value", ""] }, "", { $concat: ["$$value", ", "] } ] },
            "$$this"
          ] }
        }
      },
      textPreview: { $substrCP: [ { $ifNull: ["$text", ""] }, 0, 80 ] }
  }},
  { $limit: 5 }
])

Note: $substrCP truncates the comment text to 80 characters for a manageable preview. Analysts can now sort, scan or filter these rows in any reporting tool.

Query patterns for complex workflows

Fundamental Principle: Actual queries in MongoDB are not simple responses. These are workflows that analysts can reuse with confidence. Each aggregation you design is more than a one-off solution — it becomes a repeatable step in a larger process, ready to be revived, adapted, or expanded.

6. General summary and best practices

What this course taught you

This course taught you hands-on, working on real-world scenarios rather than stopping at basic theory or syntax. By working on realistic scenarios, you saw how the building blocks of MongoDB come together to solve practical challenges that analysts face every day.

Synthetic best practices

Dates:

• Always use ISODate() — never use strings for date comparisons.
• Use $type: "date" in $match stages to avoid errors on mixed fields.
• Prefer $dateDiff for readable interval calculations, $subtract for millisecond calculations.
• Use $dateToString with formats suitable for destination tools.
• Check data quality with a census type before any temporal analysis.

Arrays:

• Use $in for simple multiple matches, $elemMatch when multiple conditions should apply to the same element.
• Use $ifNull to protect $size queries against missing fields.
• $unwind creates intentional duplicates — use them for element-wise scans.
• $addToSet in $group deduplicates values ​​— ideal for counting unique movies per actor.

Nested data:

• The dot notation (awards.wins) allows navigation in subdocuments.
• cast.0 accesses the first element of an array by index.
• Project nested values ​​into top-level fields for clean outputs.
• $objectToArray converts a subdocument into an array of key-value pairs for dynamic analysis.
• $arrayElemAt extracts a specific element from an array by index (useful for geographic coordinates).

Actual analysis workflows:

• Sub-pipelines in $lookup allow rich calculations on the fly.
• $setWindowFields for moving averages and running totals.
• $reduce to concatenate arrays into CSV strings.
• $topN for rankings by group.
• $setUnion to deduplicate arrays before analysis.

Key Operators Quick Reference

Training based on the sample_mflix dataset from MongoDB Atlas. All queries are compatible with MongoDB 6.0+.

Search Terms

dates · arrays · nested · data · mongodb · nosql · databases · sql · date · year · query · array · fields · films · format · patterns · filter · flatten · genre · group · length · month · release · released