php mongo aggregate project to date field

Question

In php I have the following mongo aggregate funnction:

array(
    array(
        '$match' => array(
            "i" =>  array('$in' => $valRefId),
            "date" => array('$gt'=> $mongotimestart , '$lte'=> $mongotimeend),
        ),
    ),
    array(
        '$project' => array(
            "i" => '$i',
            "v" => '$v',
            "datumpje" => array('$add'=>array('$date',$timezone_date)),
            "year" => array('$year' =>array('$add'=>array('$date',$timezone_date))),
            "month" => array('$month'=>array('$add'=>array('$date',$timezone_date))),
            "day" => array('$dayOfMonth'=>array('$add'=>array('$date',$timezone_date))),
            "hour" => array('$hour'=> array('$add'=>array('$date',$timezone_date))),
        )
    ),
    array(
        '$group' => array(
            '_id' => array(
                "valRefId" => '$i',
                "year" => '$year',
                "month" => '$month',
                "day" => '$day',
                "hour" => '$hour',
            ),
            "MaxValue" => array('$max' => '$v'),
            "MinValue" => array('$min' => '$v'),
            "Gemiddelde" => array('$avg' => '$v'),
            "Aantal" => array('$sum' => 1),
        )
    ),
    array(
        '$sort' => array(
            "_id.valRefId" => 1,
            "_id.year" => 1,
            "_id.month" => 1,
            "_id.day" => 1,
            "_id.hour" => 1,
        )
    ),
    array(
        '$project' => array(
            "date" => array(
                '$concat' => array(array('$substr' => array('$_id.year',0,4)),
                    "-",
                    array('$substr' => array('$_id.month',0,2)),
                    "-",
                    array('$substr' => array('$_id.day',0,2)),
                    " ",
                    array('$substr' => array('$_id.hour',0,2)),
                    ":00:00",
                ),
            ),
            "MaxValue" => '$MaxValue',
            "MinValue" => '$MinValue',
            "Gemiddelde" => '$Gemiddelde',
            "Aantal" => '$Aantal',
        )
    ),
);

The meaning of the function is get the maxValue, MinValue, Average and number of records in a time window where $i is in a given array ($valRefId) and then group by $i, $year,$month,$day and $hour.

As result I now have the "_id". "date", "maxValue", "minValue", "Gemiddelde", "Aantal".

My question is now, is it possible to return the "date" field not in string type, but as a Date type (or timestamp type)?

chridam chridam · Accepted Answer · 2016-09-06T13:18:43

Yes it's possible. You can return the date field in the $group pipeline using the $first accumulator as follows:

$pipeline = array(
    array(
        '$match' => array(
            "i" =>  array('$in' => $valRefId),
            "date" => array('$gt'=> $mongotimestart , '$lte'=> $mongotimeend),
        ),
    ),
    array(
        '$project' => array(
            "i" => 1,
            "v" => 1,
            "datumpje" => array('$add'=>array('$date',$timezone_date)),
            "year" => array('$year' =>array('$add'=>array('$date',$timezone_date))),
            "month" => array('$month'=>array('$add'=>array('$date',$timezone_date))),
            "day" => array('$dayOfMonth'=>array('$add'=>array('$date',$timezone_date))),
            "hour" => array('$hour'=> array('$add'=>array('$date',$timezone_date))),
        )
    ),
    array(
        '$group' => array(
            '_id' => array(
                "valRefId" => '$i',
                "year" => '$year',
                "month" => '$month',
                "day" => '$day',
                "hour" => '$hour',
            ),
            "date" => array('$first' => '$datumpje'),
            "MaxValue" => array('$max' => '$v'),
            "MaxValue" => array('$max' => '$v'),
            "MinValue" => array('$min' => '$v'),
            "Gemiddelde" => array('$avg' => '$v'),
            "Aantal" => array('$sum' => 1),
        )
    ),
    array(
        '$sort' => array(
            "_id.valRefId" => 1,
            "_id.year" => 1,
            "_id.month" => 1,
            "_id.day" => 1,
            "_id.hour" => 1,
        )
    )
);

php mongo aggregate project to date field

1 Answers