Implement a query similar to MySQL Union with MongoDB?

To implement a query similar to MySQL UNION in MongoDB, use the $lookup aggregation stage to join collections, followed by $group and $project to combine and format results. This approach joins collections based on matching fields rather than simply concatenating rows.

Syntax

db.collection1.aggregate([
    { $lookup: { 
        from: "collection2", 
        localField: "matchField", 
        foreignField: "matchField", 
        as: "joinedData" 
    }},
    { $unwind: "$joinedData" },
    { $group: { _id: { field: "$matchField" }, combinedValue: { $sum: "$field + $joinedData.field" } }},
    { $project: { field: "$_id.field", combinedValue: 1, _id: 0 }}
]);

Sample Data

Create first collection with Amount data:

db.demo486.insertMany([
    {_id: 1, "Amount": 30, "No": 4},
    {_id: 2, "Amount": 40, "No": 2},
    {_id: 3, "Amount": 60, "No": 6}
]);

Display the first collection:

db.demo486.find();

{ "_id" : 1, "Amount" : 30, "No" : 4 }
{ "_id" : 2, "Amount" : 40, "No" : 2 }
{ "_id" : 3, "Amount" : 60, "No" : 6 }

Create second collection with Price data:

db.demo487.insertMany([
    {_id: 1, "Price": 10, "No": 4},
    {_id: 2, "Price": 80, "No": 9},
    {_id: 3, "Price": 20, "No": 6}
]);

{ "_id" : 1, "Price" : 10, "No" : 4 }
{ "_id" : 2, "Price" : 80, "No" : 9 }
{ "_id" : 3, "Price" : 20, "No" : 6 }

Example: Join and Combine Collections

Perform a UNION-like operation to combine Amount and Price for matching No values:

db.demo486.aggregate([
    {$lookup: { 
        from: "demo487", 
        localField: "No", 
        foreignField: "No", 
        as: "demo487"
    }},
    {$unwind: "$demo487"},
    {
        $group: {
            _id: {
                No: "$No"
            },
            TotalValue: { $sum: { $add: [ "$Amount", "$demo487.Price" ] }}
        }
    },
    {$sort: {"_id.No": 1}},
    {
        $project: {
            No: "$_id.No",
            TotalValue: 1,
            _id: 0
        }
    }
]);

{ "TotalValue" : 40, "No" : 4 }
{ "TotalValue" : 80, "No" : 6 }

How It Works

• $lookup: Joins collections based on matching "No" field values
• $unwind: Deconstructs the joined array to work with individual documents
• $group: Combines Amount and Price using $add and $sum
• $project: Formats the final output structure

Conclusion

MongoDB's aggregation pipeline with $lookup provides UNION-like functionality by joining collections on matching fields. This approach combines data from multiple collections into unified results, similar to SQL UNION operations.