Mongodb聚合排序关联数组

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.

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}});