Creating Inner Join And Left Join Lookup Views in MongoDB: A Step-by-Step Guide

Learn how to create inner and left join lookup MongoDB views by aggregating data from multiple collections and effectively handling missing values.

https://www.pexels.com/photo/silver-chain-145683/

In this blog post, we'll delve into MongoDB's left and inner join lookup views, using real-world examples and code snippets to highlight their differences. Before we start, I want to mention that inner and left joins are relational database concepts. These terms are not widely used in NoSQL. But in real-world projects, there will be times when you will need to aggregate data from multiple collections using one of those lookup techniques. So, I will stick with inner and left join terminology because it is familiar to most developers.

I assume you are familiar with creating and querying views. If not, you can check out my previous article, Editing Creating and Querying Views in MongoDB. 

The Initial Collections

To clarify things, we'll work with two collections: aircraft and flights. The aircraft collection holds data about different aircraft models, while the flights collection contains flight information, including the aircraft used for each flight. We'll create a view that combines data from both collections, showing the departure and arrival locations, the aircraft model, and the airline.

Let's get started by populating the collections with a couple of sample documents:

// Populating the aircraft collection
db.aircraft.insertMany([
  { id: 1, model: 'Boeing 737', range: 3500, airline: 'Airline A' },
  { id: 2, model: 'Airbus A320', range: 3000, airline: 'Airline B' }
]);

// Populating the flights collection
db.flights.insertMany([
  { id: 101, from: 'City X', to: 'City Y', aircraftId: 1, departureDate: ISODate('2023-08-15') },
  { id: 102, from: 'City Y', to: 'City Z', aircraftId: 2, departureDate: ISODate('2023-08-16') },
  { id: 103, from: 'City Z', to: 'City X', aircraftId: 3, departureDate: ISODate('2023-08-17') }
]);

Inner Join Lookup View

Switching gears to the inner join scenario, we can achieve this by once again using the $lookup stage coupled with $unwind, followed by a filtering $match stage:

db.createView("innerJoinView", "flights", [
  {
    $lookup: {
      from: "aircraft",
      localField: "aircraftId",
      foreignField: "id",
      as: "aircraftInfo"
    }
  },
  {
    $unwind: "$aircraftInfo"
  },
  {
    $match: { aircraftInfo: { $exists: true } }
  },
  {
    $project: {
      from: 1,
      to: 1,
      aircraftModel: "$aircraftInfo.model",
      airline: "$aircraftInfo.airline"
    }
  }
]);

By incorporating the $match stage after $unwind, we ensure that only documents with matching aircraft information are retained, simulating an inner join.

Left Join Lookup View

A left join in MongoDB can be simulated using the $lookup stage, which joins the collections and creates an array of matching documents. However, by combining this with the $unwind operation, we can eliminate the need for arrays and present the data more cleanly:

db.createView("leftJoinView", "flights", [
  {
    $lookup: {
      from: "aircraft",
      localField: "aircraftId",
      foreignField: "id",
      as: "aircraftInfo"
    }
  },
  {
    $unwind: "$aircraftInfo"
  },
  {
    $project: {
      from: 1,
      to: 1,
      aircraftModel: "$aircraftInfo.model",
      airline: "$aircraftInfo.airline"
    }
  }
]);

In this succinct example, the $unwind stage gracefully flattens the aircraftInfo array, allowing us to access the fields directly within the subsequent stages.

Default Values in Left Join Lookup

To handle instances where there are no matching entries in the aircraft collection, we can incorporate the $ifNull operator, maintaining the elegance of the $unwind operation:

db.createView("leftJoinDefaultView", "flights", [
  {
    $lookup: {
      from: "aircraft",
      localField: "aircraftId",
      foreignField: "id",
      as: "aircraftInfo"
    }
  },
  {
    $unwind: { path: "$aircraftInfo", preserveNullAndEmptyArrays: true }
  },
  {
    $project: {
      from: 1,
      to: 1,
      aircraftModel: { $ifNull: ["$aircraftInfo.model", "Unknown Model"] },
      airline: { $ifNull: ["$aircraftInfo.airline", "Unknown Airline"] }
    }
  }
]);

By utilizing $unwind with the preserveNullAndEmptyArrays option, we gracefully handle scenarios where there are no matches in the aircraft collection while the $ifNull operator assigns default values.