MongoDB NoSQL Data Engineering

MongoDB aggregation pipelines in practice

Four queries against a mixed movie/client dataset — and the pipeline stages that made each one possible without pulling data into Python first.

The natural instinct when querying NoSQL data is to pull it into pandas and filter there. That works up to a point — until the collection is large, or you need to join across embedded documents, or you want the database to do the heavy lifting before network transfer. MongoDB’s aggregation pipeline is the answer, and the four queries I worked through showed how differently it thinks about data compared to SQL.

The dataset

A single collection (dump_all) containing heterogeneous documents: some are client records (name, surname, email, address), others are movie records (title, year, genre, director, budget, gross). Mixed-type collections are unusual coming from a relational background — the aggregation pipeline handles it by using $match early to isolate the document type you care about.

Query 1: Clients without a second surname

db.dump_all.find({
  "client.surname2": { $exists: false },
  "client.nationality": "Spanish"
})

Simple enough with find() — but the interesting part was that $exists: false is more reliable here than checking for null, because the field simply isn’t present in documents where there’s no second surname. Schema flexibility cuts both ways.

Query 2: Emails where the nickname contains a digit

db.dump_all.find({
  "client.email": { $regex: /^[^@]*[0-9][^@]*@/ }
})

The regex anchors to the left of the @ symbol and checks for any digit in that prefix. In SQL this would be a LIKE or REGEXP_LIKE — MongoDB’s $regex operator maps directly, though it bypasses indexes unless combined with a prefix match.

This one needed the full pipeline: unwind the genres array, group by year and genre, count, then find the max count per year.

db.dump_all.aggregate([
  { $match: { "movie.year": { $exists: true } } },
  { $unwind: "$movie.genres" },
  { $group: {
      _id: { year: "$movie.year", genre: "$movie.genres" },
      count: { $sum: 1 }
  }},
  { $sort: { "_id.year": 1, count: -1 } },
  { $group: {
      _id: "$_id.year",
      topGenre: { $first: "$movie.genres" },
      count: { $first: "$count" }
  }}
])

The double-group pattern — first to count, then to pick the winner per year — is the MongoDB equivalent of a SQL ROW_NUMBER() OVER (PARTITION BY year ORDER BY count DESC). It’s more verbose but composable: each stage is testable independently.

Query 4: Director stats (film count, avg budget, avg gross)

db.dump_all.aggregate([
  { $match: {
      "movie.details.director": { $exists: true },
      "movie.details.budget": { $exists: true },
      "movie.details.gross": { $exists: true }
  }},
  { $group: {
      _id: "$movie.details.director",
      filmCount: { $sum: 1 },
      avgBudget: { $avg: "$movie.details.budget" },
      avgGross: { $avg: "$movie.details.gross" }
  }}
])

The $match before $group is critical here — documents that don’t have all three fields would skew the averages or inflate the count. In SQL, WHERE filtering happens implicitly; in the aggregation pipeline you have to be explicit about it.

Full notebook: mongodb.md

← Back to all posts