CODEWITHSUNDEEP
c#mongodbmongodb-.net-drivermongodb-queryaggregation-framework
Kyojimaru
Kyojimaru

Reputation: 2724

Sort data by Sum from 2 fields and Sort Descending

I have a collection with this document in MongoDB

string _id;
string item;
string category;
int mobileView;
int webView;

I want to take 10 items sorted descending by the total sum of mobileView and webView, is there anyway I can achieve this directly just by querying, or should I get all the data and loop through it all and sum the mobileView and webView manually before taking only the 10 with highest total?

I've been reading about the Aggregation Framework, but from what I understand, it Sum only one field that's been grouped by the query. Did what I understand is wrong, if so how to do it using the Aggregation Framework.

(I don't want the total Sum of the View to be grouped by the same category, I need it to only Sum the View on each item before sort it)

Upvotes: 1

Views: 1648

Answers (2)

Tom
Tom

Reputation: 1

I actually wrote a Linq provider, MongoLinqPlusPlus, to generate simple aggregation framework pipelines such as yours.

So with MongoLinqPlusPlus your query simply becomes:

collection.QueryablePlusPlus()
          .Select(c => new {
              OriginalDoc = c,
              SortKey = c.mobileView + c.webView
          })
          .OrderByDescending(c => c.SortKey)
          .Take(10)
          .Select(c => c.OriginalDoc)

(* It would be nice if we could do the arithmetic in the sort (.OrderByDescending(c => c.mobileView + c.webView) but the aggregation framework doesn't support this directly. So I chose to require the developer the explicitly create the projection rather than have it implicitly created by the Linq provider.)

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151122

The aggregation framework is what you want here. Not only for "aggregating" but for "manipulation" of documents as well. So basically you want the $add operator and the $sort pipeline to get your desired result.

Will take me a few moments to fire up a Visual Studio, so here is the shell form that is not hard to translate:

db.collection.aggregate([
    { "$project": {
        "item": 1,
        "category": 1,
        "mobileView": 1,
        "webView": 1,
        "totalView": { "$add": [ "$mobileView", "$webView" ] }
    }},
    { "$sort": { "totalView": -1 } }
])

So basically "calculate" a field and then use that "projected" result as the field to $sort on.

Just BSON documents, so it should not be to hard.

Specifically for C# syntax do something like this:

var project = new BsonDocument {
    {
        "$project",
        new BsonDocument()
            .Add( "item", 1 )
            .Add( "category", 1 )
            .Add( "mobileView", 1 )
            .Add( 
                "totalView",
                new BsonDocument()
                    .Add( "$add", new BsonArray {
                        "$mobileView", "$webView"
                    })
            )
    } 
};

var sort = new BsonDocument {
    {
        "$sort",
        new BsonDocument {
            { "totalView", -1 }
        }
    }
};

var pipeline = new[] { project, sort };
var results = collection.Aggregate(pipeline);

All of the $project items just as with $group are "explicitly" named fields to include in the stage output. In the aggregation framework, the fields you want are "all or nothing" and you must specify each one you expect to be visible in the next pipeline stage or indeed at the end of the pipeline. Fields not mentioned no longer exist as far as the pipeline is concerned.

Think unix style "pipe" | where you "chain" results of one command to another, which is a clear analogy for what is happening here.

Upvotes: 3

Related Questions