Mongodb aggregate $sort by $match

Question

I have a user collection with sub document of 'music' that has a sub document of 'likes'. I'll like to run a search and find the top 10 users who liked a specific artist the most, sorted by how much they liked them. this is how the dataset is structured

[
{
    '_id' : ObjectId("507f1f77bcf86cd799439011"),
    'user_name' : "John",
    'music' : [
        'likes' [
            {'name': 'david bowie', 'strength': 50 },
            {'name': 'john lennon', 'strength': 100 },
            {'name': 'bob marley', 'strength': 20 },
        ]
    ]
},
{
    '_id' : ObjectId("54304264e77cc5a1670cb318"),
    'user_name' : "Paul",
    'music' : [
        'likes' [
            {'name': 'david bowie', 'strength': 60 },
            {'name': 'john lennon', 'strength': 70 },
            {'name': 'bob marley', 'strength': 100 },
        ]
    ]
}
]

I've been trying to use the following aggregate command:

$artist = "david bowie";
$db->collection->aggregate( 
        array( 
            array( '$project' => array( 'Likes' => '$music.likes' ) ),
            array( '$match' => array( 'Likes.name' => $artist ) ),
            array( '$sort' => array( 'Likes.strength' => 1 ) ),
            array( '$limit' => 10 )
            )
        );

the match does works, but it only sort the Likes not the overall results. also - is there a way not to return all the items in the Likes document but just the one that is related to the match?

here is the results I'm getting

[
{ 
    ["_id"]=> object(MongoId)#310 (1) { ["$id"]=> string(24) "507f1f77bcf86cd799439011",
    ["Likes"] => array(49) { 
        [0]=> array(2) { ["name"]=> string(11) "john lennon" ["strength"]=> float(100) },
        [1]=> array(2) { ["name"]=> string(11) "david bowie" ["strength"]=> float(50) },
        [2]=> array(2) { ["name"]=> string(11) "bob marley" ["strength"]=> float(20) },
        ...
    }
},
{ 
    ["_id"]=> object(MongoId)#310 (1) { ["$id"]=> string(24) "54304264e77cc5a1670cb318",
    ["Likes"] => array(49) { 
        [0]=> array(2) { ["name"]=> string(11) "bob marley" ["strength"]=> float(100) },
        [1]=> array(2) { ["name"]=> string(11) "john lennon" ["strength"]=> float(70) },
        [2]=> array(2) { ["name"]=> string(11) "david bowie" ["strength"]=> float(60) },
        ...
    }
}   
]

should I be using a different combination of commands in the aggregate?

Neil Lunn Neil Lunn · Accepted Answer · 2014-10-23T02:54:19

So the thing to be aware of here is that "Likes" (from projection) is an array embedded in the document object. This means that while sub-fields like "strength" will be considered, what is actually being considered is every element in the array and each sub-field value.

So there is nothing intrinsically wrong with the approach here but for when you are dealing with arrays in the aggregation framework you generally want to use $unwind first. Depending of course on where your intention here is to "filter" the array content or not there are basically two approaches:

$artist = "david bowie";
$db->collection->aggregate( 
        array( 
            array( '$match' => array( 'music.likes.name' => $artist ) ),
            array( '$project' => array( 'Likes' => '$music.likes' ) ),
            array( '$unwind' => '$Likes' ),
            array( '$match' => array( 'Likes.name' => $artist ) ),
            array( '$group' => array( 
                '_id' => '$_id', 
                'Likes' => array( '$push' => '$Likes' )
            )),
            array( '$sort' => array( 'Likes.strength' => -1 ) ),
            array( '$limit' => 10 )
            )
        );

Which essentially "filters" the content of the array in each document to just the elements that match the "artist" condition, so here the only items left to sort on are those that match.

$db->collection->aggregate( 
        array( 
            array( '$match' => array(music.likes.name' => $artist ) ),
            array( '$project' => array( 'Likes' => '$music.likes' ) ),
            array( '$unwind' => '$Likes' ),
            array( '$group' => array( 
                '_id' => '$_id', 
                'Likes' => array( '$push' => '$Likes' ),
                'strength' => array(
                    '$max' => array(
                        '$cond' => array(
                            array( '$eq' => array( '$Likes.name', $artist ) ),
                            '$Likes.strength',
                            0
                        )
                    )
                )
            )),
            array( '$sort' => array( 'strength' => -1 ) ),
            array( '$limit' => 10 )
            )
        );

In the second case you are basically "building" an additional field that inspects the elements in the array and determines whether to "use" that value where it matches the "artist" with an $eq test inside the $cond operator as a ternary condition.

As this occurs within the $group stage, it sort of makes sense here to just apply the $max value found in the matching elements of the array, where of course a value of 0 is returned from the test for array items that did not match the condition.

The only other things of note there is using the $match stage first. You generally want to "filter" conditions on your documents first to avoid unnecessary work. This is also your only chance for pipeline to utilize and "index" on your collection, and you will want that. Of course it also makes sense to $sort in reverse with the highest "strength" values on top.

It just comes down to whether you want to "filter" the array or just return the whole content but determine a value on which to sort on.

Mongodb aggregate $sort by $match

2 Answers