Mongodb聚合排序关联数组

Question

As $ positional operator doesn't work 2 level deep nested array, I am using alternative schema to enable update function for nested array.

I have a nested documents as follows

{
  '_id' : 1234,
  'bio' : {
     'achievements' : {
        'Yhg87Hghg65' : {
           'title' : 'Achievement 1',
           'score' : 95,
           'year' : 2004
        },
        '67gjfygt8Hd' : {
           'title' : 'Achievement 2',
           'score' : 89,
           'year' : 2003
        },
        'Lkoh8hHggf7' : {
           'title' : 'Achievement 1',
           'score' : 90,
           'year' : 2005
        }
     }
  }
}

Now, using mongodb aggregation pipeline, I can fetch this as follow in PHP

$doc = $collection -> aggregate(
  array(
    '$match' => array(
        '_id' => 1234
     )
  ),
  array(
    '$project' => array(
       'bio.achievements' => 1
    )
  )
);

Until now, everything works fine. But I need to sort achievements by year. Using normal '$sort' before '$project' wont work because because achievements aren't arrays, they are fields whose values are array. If anyone know how to achieve it, please give me some hints.

Answer 1

When you have your objects in a map, in javascript, you can never guarantee the order of retrieval. Nor has MongoDb any in-built methods to sort the properties of a map. So you need to do the ordering in the client side. Your code may look like:

var result = db.collection.find({"_id":1234},{"bio.achievements":1}).
       map(function(doc){
            var achievements = (Object.keys(doc.bio.achievements)).
            map(function(key){
            doc.bio.achievements[key]["key"] = key;
            return doc.bio.achievements[key];
           });
           achievements.sort(function(a,b){
           return a.year-b.year;
         })
      doc.bio.achievements = achievements;
      return doc;
  })

Having said this, the best way to store ordered objects is to store them in an array. If you could change your schema as below:

{
  '_id' : 1234,
  'bio' : {
     'achievements' : [
        {  'id':'Yhg87Hghg65',
           'title' : 'Achievement 1',
           'score' : 95,
           'year' : 2004
        },
        {
           'id':'67gjfygt8Hd',
           'title' : 'Achievement 2',
           'score' : 89,
           'year' : 2003
        },
        {  'id':'Lkoh8hHggf7',
           'title' : 'Achievement 1',
           'score' : 90,
           'year' : 2005
        }
     ]
  }
}

The you could easily aggregate the desired result:

db.collection.aggregate([
{$match:{"_id":1234}},
{$unwind:"$bio.achievements"},
{$sort:{"bio.achievements.year":1}},
{$group:{"_id":"$_id","achievements":{$push:"$bio.achievements"}}}
])

Sample o/p:

{
        "_id" : 1234,
        "achievements" : [
                {
                        "id" : "67gjfygt8Hd",
                        "title" : "Achievement 2",
                        "score" : 89,
                        "year" : 2003
                },
                {
                        "id" : "Yhg87Hghg65",
                        "title" : "Achievement 1",
                        "score" : 95,
                        "year" : 2004
                },
                {
                        "id" : "Lkoh8hHggf7",
                        "title" : "Achievement 1",
                        "score" : 90,
                        "year" : 2005
                }
        ]
}

As $ positional operator doesn't work 2 level deep nested array, I am using alternative schema to enable update function for nested array.

You need to design your schema in such a way that the update can be made using the $ operator, as far as the posted example is in question, an update can be made with the positional operator, similar to an operation below:

    db.collection.update({"bio.achievements.id":"Yhg87Hghg65"},
                         {$set:{"bio.achievements.$.year":2006}});