cakephp 2.0 - How to select subdocuments with MongoDB

Question

I have a collection with a subdocument tags like :

Collection News :
   title (string)
   tags: [tag1, tag2...]

I want to select all the tags who start with a pattern, but returning only the matching tags.

I already use a regex but it returns all the news containing the matching tag, here is the query :

db.news.find( {"tags":/^proga/i}, ["tags"] ).sort( {"tags":1} ).
    limit( 0 ).skip( 0 )

My question is : How can I retrieve all the tags (only) who match the pattern ? (The final goal is to make an autocomplete field)

I also tried using distinct, but I didn't find a way to make a distinct with a find, it always returning me all the tags :(

Thanks for your time

Answer 1

A bit late to the party, but hopefully will help others who are hunting for a solution. I've found a way to do this using the aggregation framework and combining $project and $unwind with the $match, by chaining them together. I've done it using PHP but you should get the gist:

    $ops = array(
        array('$match' => array(
                'collectionColumn' => 'value',
            )
        ),
        array('$project' => array(
                'collection.subcollection' => 1
            )
        ),
        array('$unwind' => '$subCollection'),
        array('$match' => array(
                subCollection.subColumn => 'subColumnValue'
            )
        )
    );

The first match and project are just use to filter out to make it faster, then the unwind on subcollection spits out each subcollection item by item which can then be filtered using the final match.

Hope that helps.

UPDATE (from Ryan Wheale):

You can then $group the data back into its original structure. It's like having an $elemMatch which returns more than one subdocument:

array('$group' => array(
        '_id' => '$_id',
        'subcollection' => array(
            '$push' => '$subcollection'
        )
    )
);

I translated this from Node to PHP, so I haven't tested in PHP. If anybody wants the Node version, leave a comment below and I will oblige.