Linq Mongodb Multiple sums as columns with where for each

advertisements

I have a Mongo Doc as Follows

{
    userid: "jd",
    fullname: "john doe",
    Tracker : [
        {
            type: "aaa",
            total: "111"
            state: "Active"
        }
        {
            type: "bbb",
            total: "222"
            state: "Active"
        }
        {
            type: "aaa",
            total: "333"
            state: "Active"
        }
        {
            type: "ccc",
            total: "111"
            state: "Active"
        }
        {
            type: "ccc",
            total: "111"
            state: "Inactive"
        }
   ]
}

My goal is to have a linq query within vb.net (or c# that i can convert to vb.net) that sums up all the totals for each type, but in columns, not rows

userid    fullname    aaa    bbb   ccc
jd        john doe     444   222   111

so far, I got: Note: _users is my mongocollection

dim results = From u in _users.AsQueryable(Of MyClass)() _
Order by _u.fullname
Where _u.Tracker.Any(Function(typestate) typestate.state="Active"
Select _u.userid, _u.fullname, totalAAA = _u.Tracker.Sum(Function(tAAA) tAAA.total),  totalBBB = _u.Tracker.Sum(Function(tBBB) tBBB.total), totalCCC = _u.Tracker.Sum(Function(tCCC) tCCC.total)

I know that totalAAA, totalBBB and totalCCC will result in the same, I need a specific were clause for each of those, just don't know how to do it, or if there is a better way

Thanks


What you are after is the aggregation pipeline. Rather than an LINQ type of query it is constructed as a series of BSON documents. See your documentation.

The pipeline structure goes as follows:

db.collection.aggregate([
    // Unwind the array
    { "$unwind": "$Tracker" }

    // Match the condition
    { "$match": { "Tracker.state": "active" } },

    // Project the output to new fields
    { "$project": {
        "user_id": 1,
        "firstname": 1,
        "aaa": { "$cond": [
            { "$eq": [ "$type", "aaa" ] },
            "$total",
            0
        ]},
        "bbb": { "$cond": [
            { "$eq": [ "$type", "bbb" ] },
            "$total",
            0
        ]}
        "ccc": { "$cond": [
            { "$eq": [ "$type", "ccc" ] },
            "$total",
            0
        ]}
    }},

    // Group the total
    { "$group": {
        "_id": {
            "user_id": "$user_id",
            "type": "$type",
        },
        "fullname": { "$first": "$fullname" },
        "aaa": { "$sum": "$aaa" },
        "bbb": { "$sum": "$bbb" },
        "ccc": { "$sum": "$ccc" }
    }},

    // Final projection to clear up
    { "$project": {
        "_id": 0,
        "user_id": 1
        "fullname": 1,
        "aaa": 1,
        "bbb": 1,
        "ccc": 1
    }}
])

So this allows you to "unwind" and "match" the items in the document array so that only the required condition is selected. And then the document is "reshaped" through "$project", then finally "grouped" in order to sum the totals.