MongoDB sort documents by array elements

Question

I have document structure like :

{
    map: 'A',
    points: [
        {
            type: 'type1',
            distanceToSpawn: 110
        },
        {
            type: 'type4',
            distanceToSpawn: 40
        },
        {
           type: 'type6',
           distanceToSpawn: 30
        }
    ]
},
{
    map: 'B',
    points: [
        {
            type: 'type1',
            distanceToSpawn: 100
        },
        {
            type: 'type2',
            distanceToSpawn: 60
        },
        {
            type: 'type3',
            distanceToSpawn: 25
        }
    ]
},
{
    map: 'C',
    points: [
        {
            type: 'type2',
            distanceToSpawn: 90
        },
        {
            type: 'type3',
            distanceToSpawn: 1
        },
        {
            type: 'type6',
            distanceToSpawn: 76
        }
    ]
}

I want to get all maps having point type type1 sorted by the distanceToSpawn in ascending order.

Expected result :

{
    map: 'B',
    points: [
        {
            type: 'type1',
            distanceToSpawn: 100
        }
    ]
},
{
    map: 'A',
    points: [
        {
            type: 'type1',
            distanceToSpawn: 110
        }
    ]
}

I've tried something like :

db.maps.find({'points.type': {$eq : 'type1'}}, {map: 1, 'points.$':1}).sort({'points.distanceToSpawn': 1}).limit(10)

But this thing not sorting maps by ascending order.

Thanks.

Sachin Bhandari · Accepted Answer · 2020-05-09 16:38:11Z

29

You cannot do that with arrays, and the main problem here is because you want the "sort" to happen on the matched element. If you want to sort results like this then you need to use .aggregate() instead. Either as:

For modern MongoDB versions:

db.maps.aggregate([
    { "$match": { "points.type": "type1" }},
    { "$addFields": {
        "order": {
            "$filter": {
              "input": "$points",
              "as": "p",
              "cond": { "$eq": [ "$$p.type", "type1" ] }
            }
        }
    }},
    { "$sort": { "order": 1 } }
])

For MongoDB 2.6 to 3.0

db.maps.aggregate([
   { $match: { 'points.type': 'type1' } },
    {
     $project: {
       points: {
        $setDifference: [
          {
            $map: {
              input: '$points',
              as: 'p',
              in: {
                $cond: [
                  { $eq: ['$$p.type', 'type1'] },
                  '$$p',
                  false,
                ]
              }
            }
          },
          [false]
        ]
      }
    }
  },
  { $sort: { 'points.distanceToSpawn': 1 } },
]);

Or less efficiently in versions prior to MongoDB 2.6:

db.maps.aggregate([
    { "$match": { "points.type": "type1" }},
    { "$unwind": "$points" },
    { "$match": { "points.type": "type1" }},
    { "$group": {
        "_id": "$_id",
        "points": { "$push": "$points" }
    }},
    { "$sort": { "points.ditanceToSpawn": 1 } }         
])

That's the only way to match the correct elements and have them considered in a "sort" operation. The default cursor sort will just otherwise consider the values for the field in the array elements that do not match your selected "type" instead.

edited May 9, 2020 at 16:38

Sachin Bhandari

5664 silver badges19 bronze badges

answered Mar 5, 2015 at 22:59

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

4 Comments

phwa4563 Over a year ago

Keep in mind that when using $unwind operator before the $sort operator it cannot take advantages of an index. (I had the same issues trying to sort by array filds using always the first item of the array for comparism.)

Graham Clark Over a year ago

For the "modern MongoDB versions" example, should the sort be on order.distanceToSpawn instead of just order? Or if not, why?

jadus Over a year ago

What index would you create for this sorting ?

xfg Over a year ago

This is a bad solution because due to the lack of an index, all data will be load into RAM and then sorted.

Collectives™ on Stack Overflow

MongoDB sort documents by array elements

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related