MongoDB order by two fields sum?

To order by two fields sum, you can use the aggregate framework. Let us first create a collection with documents

> db.orderByTwoFieldsDemo.insertOne({"Value1":10,"Value2":35});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5ca285576304881c5ce84baa")
}
> db.orderByTwoFieldsDemo.insertOne({"Value1":12,"Value2":5});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5ca2855f6304881c5ce84bab")
}
> db.orderByTwoFieldsDemo.insertOne({"Value1":55,"Value2":65});
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5ca285686304881c5ce84bac")
}

Following is the query to display all documents from a collection with the help of find() method

> db.orderByTwoFieldsDemo.find().pretty();

This will produce the following output

{
   "_id" : ObjectId("5ca285576304881c5ce84baa"),
   "Value1" : 10,
   "Value2" : 35
}
{
   "_id" : ObjectId("5ca2855f6304881c5ce84bab"),
   "Value1" : 12,
   "Value2" : 5
}
{
   "_id" : ObjectId("5ca285686304881c5ce84bac"),
   "Value1" : 55,
   "Value2" : 65
}

Case 1: Following is the query to order by two fields sum and get the result in ascending order:

> db.orderByTwoFieldsDemo.aggregate(
... [
... {$project:{Value1:1, Value2:1, orderBySumValue:{$add: ["$Value1", "$Value2"]}}},
... {$sort:{orderBySumValue:1}}]
... );

This will produce the following output

{ "_id" : ObjectId("5ca2855f6304881c5ce84bab"), "Value1" : 12, "Value2" : 5,
"orderBySumValue" : 17 }
{ "_id" : ObjectId("5ca285576304881c5ce84baa"), "Value1" : 10, "Value2" : 35,
"orderBySumValue" : 45 }
{ "_id" : ObjectId("5ca285686304881c5ce84bac"), "Value1" : 55, "Value2" : 65,
"orderBySumValue" : 120 }

Case 2: Following is the query to order by two fields sum and get the result in descending order

> db.orderByTwoFieldsDemo.aggregate( [ {$project:{Value1:1, Value2:1,
orderBySumValue:{$add: ["$Value1", "$Value2"]}}}, {$sort:{orderBySumValue:-1}}] );

This will produce the following output

{ "_id" : ObjectId("5ca285686304881c5ce84bac"), "Value1" : 55, "Value2" : 65,
"orderBySumValue" : 120 }
{ "_id" : ObjectId("5ca285576304881c5ce84baa"), "Value1" : 10, "Value2" : 35,
"orderBySumValue" : 45 }
{ "_id" : ObjectId("5ca2855f6304881c5ce84bab"), "Value1" : 12, "Value2" : 5,
"orderBySumValue" : 17 }

Arjun Thakur

Published on 15-Apr-2019 07:41:24

• Related Questions & Answers
• MySQL query to order by two fields and NULL values in chronological order?
• MongoDB query to sum specific fields
• Merge two array fields in MongoDB?
• How to ORDER BY grouped fields in MySQL?
• MongoDB query condition to compare two fields?
• Is it possible to sum two fields in MongoDB using the Aggregation framework?
• Filtering MongoDB items by fields and subfields?
• Count by multiple fields with MongoDB aggregation
• Find sum of fields inside array in MongoDB?
• How to order by timestamp (descending order) in MongoDB
• Best way to sum array size fields in MongoDB?
• Maximum sum by picking elements from two arrays in order in C++ Program
• Order a MySQL table by two columns?
• How to compare two fields in aggregation filter with MongoDB?
• Using group by on two fields and count in MySQL?