根据数组字段大小对文档进行排序

Question

I'm trying to use the size of an array called "empofthemonth" to sort each field returned by their amount of employee of the month wins.

Here is the insert code:

$db->users->insert(
    ["firstname" => "firstname1", 
     "lastname" => "test", 
     "email" => "[e-mail]", 
     "keyinfo" => 
        ["age" => 22,
         "gender" => "Male",
         "yearsemployed" => 1,
         "empofthemonth" => ["Apr"]]
     ]);

$db->users->insert(
    ["firstname" => "firstname2", 
     "lastname" => "test2", 
     "email" => "[e-mail]", 
     "keyinfo" => 
        ["age" => 24,
         "gender" => "Female",
         "yearsemployed" => 5,
         "empofthemonth" => ["Feb", "Mar"]]
     ]);

$db->users->insert(
    ["firstname" => "firstname3", 
     "lastname" => "test2", 
     "email" => "[e-mail]", 
     "keyinfo" => 
        ["age" => 31,
         "gender" => "Female",
         "yearsemployed" => 2,
         "empofthemonth" => ["Jan", "May", "Jun"]]
     ]);

I realise that aggregation might be used but i cannot work out the full syntax.

To conclude the query results should be in this order:

1. firstname3 (3 emp of the months)
2. firstname2 (2)
3. firstname1 (1)

Answer 1

We need to $project our documents and return the $size then $sort each document by that "size" in descending order. Note that to access the array field, we need to use the "dot notation".

db.users.aggregate(
    [
        { "$project": { 
            "firstname": 1, 
            "lastname": 1, 
            "email": 1, 
            "keyinfo": 1, 
            "sz": { "$size": "$keyinfo.empofthemonth" } 
        }}, 
        { "$sort": { "sz": -1 } } 
    ]
)

---

---

Everything in PHP:

$db->users->aggregate(
    [
        [ "$project" => [ 
            "firstname" => 1, 
            "lastname" => 1, 
            "email" => 1, 
            "keyinfo" => 1, 
            "sz" => [ "$size" => "$keyinfo.empofthemonth" ]
        ]], 
        [ "$sort" => [ "sz" => -1 ] ] 
    ]
)