Listing Common Data from Two Collections Based on Foreign Key in MongoDB

A guide on how to list common data from two collections in MongoDB based on a foreign key, utilizing the aggregate method for efficient querying.

In this article, we will explore how to use MongoDB to list common data from two collections through a foreign key. We will utilize the aggregate method to perform complex queries.

MongoDB code

// Assuming we have two collections: 'users' and 'orders'
// 'users' has _id as the primary key and 'orders' has userId linked to users' _id

// Data in the 'users' collection
db.users.insertMany([
    { _id: 1, name: "Alice" },
    { _id: 2, name: "Bob" },
    { _id: 3, name: "Charlie" }
]);

// Data in the 'orders' collection
db.orders.insertMany([
    { orderId: 101, userId: 1, item: "Laptop" },
    { orderId: 102, userId: 2, item: "Phone" },
    { orderId: 103, userId: 1, item: "Tablet" }
]);

// Query to list common data from both collections
db.users.aggregate([
    {
        $lookup: {
            from: "orders",
            localField: "_id",
            foreignField: "userId",
            as: "user_orders"
        }
    },
    {
        $match: {
            "user_orders": { $ne: [] }
        }
    }
]);

Detailed explanation

• Data in the users collection: Create a users collection with fields _id and name.
• Data in the orders collection: Create an orders collection with fields orderId, userId (linked to users _id), and item.
• aggregate query:
  • $lookup: Joins the two collections. from specifies the collection to join, localField is the field in the current collection (users), foreignField is the field in the joined collection (orders), and as specifies the name of the resulting array.
  • $match: Filters records with a non-empty user_orders array, meaning only users with orders are retrieved.

System Requirements:

• MongoDB 4.0 or later

How to install MongoDB:

You can install MongoDB from the official MongoDB website or use management tools like MongoDB Compass.

Tips:

• Use aggregate with $lookup to perform complex queries in MongoDB, similar to SQL JOIN.
• Make sure to create indexes on fields used in queries to optimize performance.

---