MongoDB get all fields + sort by value computed from multiple fields

Question

I need to get documents from collection in MongoDB. A single document looks like this:

{
    name    : "John Doe",
    address : "Some cool address",
    value1  : 2,
    value2  : 3,
    value3  : 5
}

I need to retrieve all fields + sort the documents descending by number equal to value1 + value2 + value3.

Only solution I found (incomplete because it doesn't retrieve all fields) looks like this:

db.someCollection.aggregate(
    {
        $project: {
            sum: { $add: [ "$value1", "$value2", "$value3" ] }
        },
        $sort: {
            sum: -1
        }
    }
)

Like I said, this returns only sum field which is not what I want.

EDIT: Also the document is a bit larger in reality so I don't want to use pseudo-solution like

$project: {
    name:    1,
    address: 1,
    ...
}

i.e. I'd like some universal reusable solution, not the case-specific one

Deka · Accepted Answer · 2015-01-02 08:57:47Z

7

You can use $$ROOT to references the root document. Keep all fields of this document in a field and try to get it after that (depending on your client system: Java, C++, ...)

db.someCollection.aggregate(
    {
        $project: {
            sum: { $add: [ "$value1", "$value2", "$value3" ] },
            document: "$$ROOT"
        },
        $sort: {
            sum: -1
        }
    }
)

answered Jan 2, 2015 at 8:57

Deka

1,35312 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zorji Over a year ago

I think it is answer is a better answer for the question

alexmngn Over a year ago

Although document creates a document field. Is there a way not to have this field?

Parvin Gasimzade · Accepted Answer · 2013-12-16 12:37:51Z

4

Try the following query :

db.myCollection.aggregate(
{$project: {name : "$name", 
            address : "$address", 
            value1 : "$value1", 
            value2 : "$value2", 
            value3 : "$value3",
            sum : {$add : ["$value1", "$value2", "$value3"]}}},
{$sort: {sum: -1}}
)

As far as I know, there is no option like sort by condition in MongoDB. Also there is no option to add all the fields into the the project by default. There was an issue for this but it is not resolved yet.

For now, another option would be to create new field (ex : totalValues) and store the sum in that field and when value fields changed. Then you can sort by totalValues.

edited Dec 16, 2013 at 12:37

answered Dec 15, 2013 at 12:43

Parvin Gasimzade

26.1k8 gold badges58 silver badges83 bronze badges

4 Comments

Michal Artazov Over a year ago

well yeah, that would work but in case the document has 20 fields, I don't want to enumerate 20 fields explicitly... there must be some universal solution. I just need to add the sum field on top without changing the rest

Parvin Gasimzade Over a year ago

Added explanation to my answer.

Michal Artazov Over a year ago

I was afraid there's no simple solution... weird, seems legit to have this functionality I need. Thanx anyway

Łukasz Mazan Over a year ago

Since Mongo3.4, there's better solution using $addFields, check the answer by Franz Kirkman

Frazer Kirkman · Accepted Answer · 2017-01-24 22:40:44Z

3

where $project: needs to specify which fields to pass through, $addFields will return all fields and add or replace specified fields.

db.someCollection.aggregate(
    {
        $addFields: {
            sum: { $add: [ "$value1", "$value2", "$value3" ] }
        },
        $sort: {
            sum: -1
        }
    }
)

will achieve exactly what you want.

Please note, this feature was added in Mongo version 3.4.

answered Jan 24, 2017 at 22:40

Frazer Kirkman

1,2041 gold badge16 silver badges25 bronze badges

2 Comments

KeitelDOG Over a year ago

This solution is good but it seems like it's not properly formatted. Like $sort should be in a nested object I think

Frazer Kirkman Over a year ago

Thanks @KeitelDOG, it was a while ago, but this works in my project.

Martin Tarjányi · Accepted Answer · 2017-04-14 13:59:33Z

1

To get back the documents as they are in the DB you can use the $replaceRoot operator:

db.someCollection.aggregate(
    [
        {
            $project: {
                sum: { $add: [ "$value1", "$value2", "$value3" ] },
                document: "$$ROOT"
            }
        },
        {
            $sort: { sum: -1 }
        },
        {
            $replaceRoot : {newRoot : "$document"}
        }
    ]
)

Result is:

{
    "_id" : ObjectId("58f0d4777bde783e55e3410b"),
    "name" : "John Doe",
    "address" : "Some cool address",
    "value1" : 2,
    "value2" : 3,
    "value3" : 5
}

{
    "_id" : ObjectId("58f0d4c77bde783e55e3411e"),
    "name" : "Jane Doe",
    "address" : "Some cool address",
    "value1" : 1,
    "value2" : 3,
    "value3" : 2
}

answered Apr 14, 2017 at 13:59

Martin Tarjányi

10.1k3 gold badges40 silver badges58 bronze badges

3 Comments

Łukasz Mazan Over a year ago

This is clever, but since $replaceRoot is available in Mongo 3.4 or newer, there's also $addFields which does exactly what the op was asking for. So there's no need for this workaround.

Martin Tarjányi Over a year ago

@ŁukaszMazan That's right. This operator is more appropriate in other scenarios.

KeitelDOG Over a year ago

I like your solution. The Cleaner one as it put the fields back to normal with no additional field name.

Collectives™ on Stack Overflow

MongoDB get all fields + sort by value computed from multiple fields

4 Answers 4

2 Comments

4 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related