Sum unique properties in different collection elements in MongoDB and get the resultant Price?

To calculate the sum of unique properties in different collection elements in MongoDB, use $cond along with $group in an aggregation pipeline. This technique allows you to conditionally select which field to use for grouping and then sum the prices for each unique identifier.

Syntax

db.collection.aggregate([
    {
        $project: {
            field1: 1,
            field2: 1,
            Price: 1,
            match: { $cond: [condition, value1, value2] }
        }
    },
    {
        $group: {
            _id: '$match',
            totalPrice: { $sum: '$Price' }
        }
    }
]);

Sample Data

db.demo580.insertMany([
    {
        "Name": "John",
        "Id1": "110",
        "Id2": "111",
        "Price": 10.5
    },
    {
        "Name": "John",
        "Id1": "111",
        "Id2": "",
        "Price": 9.5
    }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e918cebfd2d90c177b5bcae"),
        ObjectId("5e918cecfd2d90c177b5bcaf")
    ]
}

Display all documents from the collection ?

db.demo580.find();

{ "_id": ObjectId("5e918cebfd2d90c177b5bcae"), "Name": "John", "Id1": "110", "Id2": "111", "Price": 10.5 }
{ "_id": ObjectId("5e918cecfd2d90c177b5bcaf"), "Name": "John", "Id1": "111", "Id2": "", "Price": 9.5 }

Example: Sum Prices by Unique IDs

Sum the prices for unique identifiers, using Id1 when Id2 is empty ?

db.demo580.aggregate([
    {
        $project: {
            Id1: 1,
            Id2: 1,
            Price: 1,
            match: {
                $cond: [
                    { $eq: ["$Id2", ""] },
                    "$Id1",
                    "$Id2"
                ]
            }
        }
    },
    {
        $group: {
            _id: '$match',
            Price: { $sum: '$Price' },
            resultId: {
                $addToSet: {
                    $cond: [
                        { $eq: ['$match', '$Id1'] },
                        null,
                        '$Id1'
                    ]
                }
            }
        }
    },
    { $unwind: '$resultId' },
    {
        $match: {
            resultId: { $ne: null }
        }
    },
    {
        $project: {
            Id1: '$resultId',
            Price: 1,
            _id: 0
        }
    }
]);

{ "Price": 20, "Id1": "110" }

How It Works

• $cond conditionally selects Id1 when Id2 is empty, otherwise uses Id2
• $group groups documents by the selected identifier and sums the Price values
• $addToSet creates a unique set of identifiers for further processing
• $unwind and $match filter out null values from the result set

Conclusion

Using $cond with $group in MongoDB aggregation allows you to conditionally select fields for grouping and calculate sums based on unique properties across different document structures.