MongoDB aggregate query using PHP driver

Question

I have a working MongoDB aggregate query which I can run via the MongoDB shell. However, I am trying to convert it to work with the official PHP Mongo driver (http://php.net/manual/en/mongocollection.aggregate.php).

Here is the working raw MongoDB query:

db.executions.aggregate( [  
   { $project : { day : { $dayOfYear : "$executed" } } },
   { $group : { _id : { day : "$day" }, n : { $sum : 1 } } } , 
   { $sort : { _id : -1 } } , 
   { $limit : 14 }
] )

Here is my attempt (not working) in PHP using the Mongo driver:

$result = $c->aggregate(array(
    '$project' => array(
        'day' => array('$dayOfYear' => '$executed')
    ),
    '$group' => array(
        '_id' => array('day' => '$day'),
        'n' => array('$sum' => 1)
    ),
    '$sort' => array(
        '_id' => 1
    ),
    '$limit' => 14
));

The error from the above PHP code is:

{"errmsg":"exception: wrong type for field (pipeline) 3 != 4","code":13111,"ok":0}

Any ideas? Thanks.

Michael Szymczak · Accepted Answer · 2014-04-18 12:52:19Z

18

The parameter in your Javascript is an array of 4 objects with one element each, in your PHP it's an associative array (object) with 4 elements. This would represent your Javascript:

$result = $c->aggregate(array(
    array(
      '$project' => array(
          'day' => array('$dayOfYear' => '$executed')
      ),
    ),
    array(
      '$group' => array(
          '_id' => array('day' => '$day'),
          'n' => array('$sum' => 1)
      ),
    ),
    array(
      '$sort' => array(
          '_id' => 1
      ),
    ),
    array(
      '$limit' => 14
    )
));

In addition, if you have at least PHP5.4, you can use simpler array syntax. Transformation to PHP is then trivial, you simply replace curly braces with square brackets and colons with arrows:

$result = $c->aggregate([
  [ '$project' => [ 'day' => ['$dayOfYear' => '$executed']  ]  ],
  [ '$group' => ['_id' => ['day' => '$day'], 'n' => ['$sum' => 1]  ] ],
  [ '$sort' => ['_id' => 1] ],
  [ '$limit' => 14 ]
]);

edited Apr 18, 2014 at 12:52

Michael Szymczak

1,33613 silver badges15 bronze badges

answered Jan 3, 2013 at 1:27

AndreKR

34k21 gold badges120 silver badges181 bronze badges

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

4 Comments

Justin Over a year ago

Wow, thanks very much. Needed a small tweak sum needs to be $sum. Just curious how were you able to whip this up so quickly?

AndreKR Over a year ago

Worked a lot with Mongo queries in PHP recently and came across this myself quite a bit. ;)

Justin Over a year ago

It is a bit nasty building aggregates.

AndreKR Over a year ago

When translating from the console to PHP it helps describing it like I did in the answer ("an array of objects with xyz being an array of ...").

Wernfried Domscheit · Accepted Answer · 2022-01-11 16:15:49Z

You can also use the JSON array directly:

$json = [
  '{ $project : { day : { $dayOfYear : "$executed" } } },
   { $group : { _id : { day : "$day" }, n : { $sum : 1 } } } , 
   { $sort : { _id : -1 } } , 
   { $limit : 14 }'
];

$pipeline = [];
foreach ($json as $stage) {
   array_push($pipeline, toPHP(fromJSON($stage)));
}
$result = $c->aggregate($pipeline);

You can also use combination like this:

use function MongoDB\BSON\toRelaxedExtendedJSON;
use function MongoDB\BSON\fromPHP;
use function MongoDB\BSON\toPHP;
use function MongoDB\BSON\fromJSON;

$operator = '$dayOfYear';
$json = [];
array_push($json, toRelaxedExtendedJSON(fromPHP(
   array('$project ' => array('day' => array($operator => '$executed')))
)));
array_push($json, 
   '{ $group : { _id : { day : "$day" }, n : { $sum : 1 } } } , 
    { $sort : { _id : -1 } } , 
    { $limit : 14 }'
);
$pipeline = [];
foreach ($json as $stage) {
   array_push($pipeline, toPHP(fromJSON($stage)));
}
$result = $c->aggregate($pipeline);

or

$operator = '$dayOfYear';
$json = [];
array_push($json, 
   '{ $group : { _id : { day : "$day" }, n : { $sum : 1 } } } , 
    { $sort : { _id : -1 } } , 
    { $limit : 14 }'
);
$pipeline = [];
array_push($pipeline, array('$project ' => array('day' => array($operator => '$executed'))));
foreach ($json as $stage) {
   array_push($pipeline, toPHP(fromJSON($stage)));
}
$result = $c->aggregate($pipeline);

If your aggregation query is dynamic or based on user-input be aware of NoSQL-Injection! https://www.acunetix.com/blog/web-security-zone/nosql-injections/

Collectives™ on Stack Overflow

MongoDB aggregate query using PHP driver

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related