Listing Data Only in table1 and Not in table2 in MongoDB

A guide on how to list data only present in `table1` and not in `table2` in MongoDB, using the aggregate method and filtering operations.

In this article, we will explore how to use MongoDB to list data that exists only in table1 and not in table2. We will use the aggregate method in combination with $lookup and $match to perform the necessary queries.

MongoDB code

// Assuming we have two collections: 'table1' and 'table2'

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

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

// Query to list data only in table1
db.table1.aggregate([
    {
        $lookup: {
            from: "table2",
            localField: "name",
            foreignField: "name",
            as: "matched_data"
        }
    },
    {
        $match: {
            "matched_data": { $eq: [] }
        }
    },
    {
        $project: {
            _id: 1,
            name: 1
        }
    }
]);

Detailed explanation

• Data in the table1 collection: Create a table1 collection with fields _id and name.
• Data in the table2 collection: Create a table2 collection with fields _id and name.
• aggregate query:
  • $lookup: Joins the two collections. from specifies the collection to join, localField is the field in the current collection (table1), foreignField is the field in the joined collection (table2), and as specifies the name of the resulting array (matched_data).
  • $match: Filters records in table1 that have no matches in table2, meaning only records with an empty matched_data array are retrieved.
  • $project: Specifies the fields to display in the final result, here it only retrieves _id and name.

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 and filter data effectively.
• It's advisable to create indexes on fields used in queries to improve query speed.

---