PHP MongoDB聚合：只有当值大于0时，如何$ sum？

Question

I am using PHP to access a MongoDB collection in which I have recorded players of a game :

{username: "John", stats: {games_played: 79, boosters_used: 1, crystals: 5}},
{username: "Bill", stats: {games_played: 0, boosters_used: 0, crystals: 20}},
{username: "Jane", stats: {games_played: 154, boosters_used: 14, crystals: 37}},
{username: "Sarah", stats: {games_played: 22, boosters_used: 0, crystals: 0}},
{username: "Thomas", stats: {games_played: 0, boosters_used: 0, crystals: 20}},

In my PHP script I am doing this to get sums and averages :

$filter = [
    ['$group' => [
        'Players count' => ['$sum' => 1],
        'avgGamesPlayed' => [
            '$avg' => '$stats.games_played'
        ],
        'TotalGamesPlayed' => [
            '$sum' => '$stats.games_played'
        ],
    ]],
    ['$sort' => [get('sort', 'count') => (int) get('sort_order', -1)]],
];

$options = [];

$m->aggregate($filter, $options);

If I echo the result I'll obtain :

Players count = 5;
avgGamesPlayed = 51;
TotalGamesPlayed = 255;

What I would like to do is to get the $sum of players where stats.games_played is greater than 0. In this particular case the result would be 3.

I know that there is a possibility to do this if I use find and '$gt' => 0 but I really need to stick with aggregate. Si I am trying to do something like this :

'Players count' => ['$sum' => ['$gt' => 0]],

But it doesn't work and I'm stuck here for weeks now, I read the doc but I'm not that familiar with MongoDB, that's why I'm calling for your knowledges.

If you have the answer to this question I'd appreciate it a lot, thank you.

Answer 1

This is where the $cond operator fits nicely. You can use it as an expression within the $sum operator's document that will evaluate the logic and returns 0 when the $stats.games_played field evaluates to 0 and 1 otherwise (that is > 0).

Following this approach will yield the desire outcome:

$pipeline = [
    ['$group' => [
        'Players count' => [
            '$sum' => [
                '$cond' => [ [ '$gt' => [ '$stats.games_played',  0 ] ], 1, 0 ] 
            ]
        ],
        'avgGamesPlayed' => [
            '$avg' => '$stats.games_played'
        ],
        'TotalGamesPlayed' => [
            '$sum' => '$stats.games_played'
        ],
    ]],
    ['$sort' => [get('sort', 'count') => (int) get('sort_order', -1)]],
];

$options = [];

$m->aggregate($pipeline, $options);