Mongo nested key sort

Question

My mongo collections contains documents like the following:

{
    "_id" : ObjectId("52de74863fcc41ddfc7b23a5"),
    "ts" : "1385969614848",
    "Info" : [
        {
            "out" : 0,
            "Type" : "1",
            "Descr" : "Null0",
        },
        {
            "out" : 10,
            "Type" : "1",
            "Descr" : "Null",
        },
        {
            "out" : 20000,
            "Type" : "1",
            "Descr" : "Null0",
        },
        {
            "out" : 70,
            "Type" : "10",
            "Descr" : "abc",
        }
    ]
}
{
    "_id" : ObjectId("52de74863fcc41ddfc7b23a6"),
    "ts" : "1385969614852",
    "Info" : [
        {
            "out" : 500,
            "Type" : "1",
            "Descr" : "Null0",
        },
        {
            "out" : 100,
            "Type" : "1",
            "Descr" : "Null",
        },
        {
            "out" : 2896,
            "Type" : "1",
            "Descr" : "Null0",
        },
        {
            "out" : 4052,
            "Type" : "10",
            "Descr" : "abc",
        }
    ]
}

I want to sort on key "out". In order to do that, I wrote the following mongo query:

db.collection_name.find({},{"Info.out":1}).sort({"Info.out":1}).pretty()

then it shows following output:

{
    "_id" : ObjectId("52fa2922d73ddc832323f402"),
    "Info" : [
        {
            "out" : 0
        },
        {
            "out" : 10
        },
        {
            "out" : 20000
        },
        {
            "out" : 70
        }
    ]
}
{
    "_id" : ObjectId("52fa292ed73ddc832323f403"),
    "Info" : [
        {
            "out" : 500
        },
        {
            "out" : 100
        },
        {
            "out" : 2896
        },
        {
            "out" : 4052
        }
    ]
}

But, I expected the output below:

    {
    "_id" : ObjectId("52fa2922d73ddc832323f402"),
    "Info" : [
        {
            "out" : 20000
        },
        {
            "out" : 70
        },
        {
            "out" : 10
        },
        {
            "out" : 0
        }
    ]
}
{
    "_id" : ObjectId("52fa292ed73ddc832323f403"),
    "Info" : [
        {
            "out" : 4052
        },
        {
            "out" : 2896
        },
        {
            "out" : 500
        },
        {
            "out" : 100
        }
    ]
}

Does anyone know how to reach the desired output?

Jinxcat · Accepted Answer · 2014-02-11 14:36:51Z

3

Using the following aggregation framework operation:

db.collection.aggregate([
{$project: {_id:1, out: "$Info.out"} },
{$unwind: "$out"},
{$sort: {_id:1, "out":-1} },
{$group: {_id:"$_id" , "Info": { $push: {"out":"$out"}} } }
])

produces:

{
    "result" : [
        {
            "_id" : ObjectId("52de74863fcc41ddfc7b23a6"),
            "Info" : [
                {
                    "out" : 4052
                },
                {
                    "out" : 2896
                },
                {
                    "out" : 500
                },
                {
                    "out" : 100
                }
            ]
        },
        {
            "_id" : ObjectId("52de74863fcc41ddfc7b23a5"),
            "Info" : [
                {
                    "out" : 20000
                },
                {
                    "out" : 70
                },
                {
                    "out" : 10
                },
                {
                    "out" : 0
                }
            ]
        }
    ],
    "ok" : 1
}

You might also want to check:

db.collection.aggregate([
{$project: {_id:1, out: "$Info.out"} },
{$unwind: "$out"},
{$sort: {_id:1, "out":-1} },
{$group: {_id:"$_id" , "Info": { $push: "$out"} } }
])

that outputs info in each document as a "clean" array:

{
    "_id" : ObjectId("52de74863fcc41ddfc7b23a6"),
    "Info" : [
        4052,
        2896,
        500,
        100
    ]
}

edited Feb 11, 2014 at 14:36

answered Feb 11, 2014 at 14:30

Jinxcat

5841 gold badge3 silver badges17 bronze badges

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

1 Comment

blacelle Over a year ago

I made a mongotry to visualize the output: mongotry.herokuapp.com/#?bookmarkId=52fb8dc14e86f9020071ed73

Collectives™ on Stack Overflow

Mongo nested key sort

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related