Problems with converting MongoDB query using aggregate to PHP

Question

I have the following (working) MongoDB query to generate a list of the hashtag count.

db.twitter.aggregate([
    { 
        $group: { 
            _id: "$status.entities.hashtags.text", 
            hashtags: { 
                $addToSet : "$status.entities.hashtags.text" 
            }
        }
    },
    { $unwind : "$hashtags" }, 
    { $unwind : "$hashtags" },
    { $group : { _id : "$hashtags", count: { $sum : 1 } } },
    { $sort : { count : -1, _id : 1 } }
]);

Now I try to convert this query to PHP code (for laravel):

$cursor = DB::collection('twitter')->raw(function($collection)
{
    return $collection->aggregate(array(
        array(
            '$group' => array(
                '_id' => '$status.entities.hashtags.text', 
                'hashtags' => array(
                    '$addToSet' => '$status.entities.hashtags.text',
                ),
            ),
        ),
        array(
            '$unwind' => '$hashtags',
        ),
        array(
            '$unwind' => '$hashtags',
        ),
        array(
            '$group' => array(
                '_id' => '$hashtags', '
                count' => array(
                    '$sum => 1',
                ),
            ),
        ),
        array(
            '$sort' => array(
                'count' => '-1', 
                '_id' => '1',
            ),
        ),
    ));
});

dd($cursor);

What I can derive from the Laravel-MongoDB docs is that the raw query input works the same as in PHP mongodb.

The error returned is this:

MongoResultException (15951)

localhost:27017: exception: the group aggregate field 'count' must be defined as an expression inside an object

Sammaye · Accepted Answer · 2014-08-09 19:58:19Z

1

You solved this but I can tell you where you was wrong:

'$sum => 1',

Should be:

array('$sum' => 1)

answered Aug 9, 2014 at 19:58

Sammaye

44k7 gold badges110 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martijn Thomas · Accepted Answer · 2014-08-09 20:22:20Z

1

Rewrote the array and now it works:

$cursor = DB::collection('twitter')->raw(function($collection)
{
    return $collection->aggregate([
        [ 
            '$group' => [ 
                '_id' => '$status.entities.hashtags.text', 
                'hashtags' => [
                    '$addToSet' => '$status.entities.hashtags.text'
                ]
            ]
        ],
        [ '$unwind' => '$hashtags' ], 
        [ '$unwind' => '$hashtags' ],
        [ '$group' => [ '_id' => [ '$toLower' => '$hashtags' ], 'count' => [ '$sum' => 1 ] ] ],
        [ '$sort' => [ 'count' => -1, '_id' => 1 ] ]
    ]);
});

Just replaced the {} by [] and the : by => and that did the trick!

edited Aug 9, 2014 at 20:22

answered Aug 9, 2014 at 19:28

Martijn Thomas

9613 gold badges13 silver badges24 bronze badges

5 Comments

Sammaye Over a year ago

Of course this is how PHP should be written now, array is so last seasons so +1

Martijn Thomas Over a year ago

@Sammaye First of all thanks!! Can I ask you a related question? How can I lowercase the check, I tried to add [ '$project' => [ 'status.entities.hashtags.text' => [ '$toLower' => '$status.entities.hashtags.text' ] ] ] but it results in this error can't convert from BSON type Array to String.

Sammaye Over a year ago

This is something that gets me about MongoDBs aggregation framework, MongoDB is flexible and lossly compared but their aggregation framework is not. You might be able to use unwind here if it doesn't break when the field is somtimes a string. However if it does I know of no truly easy way, I dont think you can do a conditional unwind currently

Martijn Thomas Over a year ago

I just got it to work! I used this line instead [ '$group' => [ '_id' => [ '$toLower' => '$hashtags' ], 'count' => [ '$sum' => 1 ] ] ], it is after the unwinds and I think that did the trick!

Sammaye Over a year ago

Nice, gotta remember that one

Collectives™ on Stack Overflow

Problems with converting MongoDB query using aggregate to PHP

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related