MongoDB group to C# API

Question

I have the following mongoDB collection of orders and I need to group them in a C# application:

    [
    {
        _id: 123,
        status: [{
                Detail: 'Started',
                Origin: 'France',
                Last: true
        }]
    },
    {
        _id: 456,
        status: [{
                Detail: 'Received',
                Origin: 'France',
                Last: true
                },{
                Detail: 'Started',
                Origin: 'Italy',
                Last: false
        }]
    },
    {
        _id: 789,
        status: [{
                Detail: 'Started',
                Origin: 'Rome',
                Last: true
        }]
    },
    {
        _id: 123,
        status: [{
                Detail: 'Received',
                Origin: 'Germany',
                Last: true
                },{
                Detail: 'Started',
                Origin: 'Spain',
                Last: false
                }]
    }
    ]

I need to return a structure with last active statuses from these orders, grouping by countries and them sum Last statuses, something like this:

    [
        {
            France: {
                Total: 2,
                Started: 1,
                Received: 1
            },
            Rome: {
                Total: 1,
                Started: 1,
                Received: 0
            },
            Germany: {
                Total: 1,
                Started: 0,
                Received: 1
            }
        }
    ]

Can you help me create the correct aggregation to create this result?

user_4368485 · Answer

you can achieve the desired result with the following aggregation pipeline:

db.orders.aggregate([
    {
        $unwind: "$status"
    },
    {
        $group: {
            _id: "$status.Origin",
            Total: { $sum: 1 },
            data: { $push: "$$ROOT" }
        }
    },
    {
        $project: {
            Country: "$_id",
            Total: 1,
            Started: {
                $size: {
                    $filter: {
                        input: "$data",
                        cond: { $eq: ["$$this.status.Detail", "Started"] }
                    }
                }
            },
            Received: {
                $size: {
                    $filter: {
                        input: "$data",
                        cond: { $eq: ["$$this.status.Detail", "Received"] }
                    }
                }
            }
        }
    }
])

here's an easy way to run this aggregation using c# with some form of type-safety.

test: https://mongoplayground.net/p/1x0wrJlTmJl

MongoDB group to C# API

1 Answers