dev nek dev nek - 2 months ago 21
PHP Question

Group by mongodate in mongodb

My insertd data is

{ "_id" : ObjectId("5992e6f52479d56b0b2709bf"),
"TIMESTAMP" : ISODate("2017-08-15T12:19:25.680+0000"),
"RECORD" : 20507361.0,
"BattV_Min" : 11.9,
"SEVolt" : 0.0
}


and my query for getting date is

$collection1 = $db->tablename;
$filter1 = array('timestamp' => array('$gte' => $acst1, '$lte' => $acst2));
$options1 = array('projection' => array('_id' => 0,'temperature' => 1,timestamp => 1));
$oresult = $collection1->find($filter1,$options1);


I am getting data in billions so I want to optimize my query using group by method, like if I have data below 70000 I will group by second if I have data range between 70000 to 100000 than group by minute so on by minute, by time and last by day.

Answer Source

you can use aggregate function to use group by in mongodb than project that date format as you want and if you want column value also than use $addToSet function and project that average value use avg function.

$collection1 = $db->tablename;
                $oresult = $collection1->aggregate([
                                [
                                    '$match' => array(TIMESTAMP => array('$gte' => $acst1, '$lte' => $acst2))
                                ],
                                [
                                    '$project' => [
                                    'year'  => ['$year' =>'TIMESTAMP'],
                                        'month' => ['$month' => '$TIMESTAMP'],
                                        'day'   => ['$dayOfMonth' => '$TIMESTAMP'],
                                        'hour'  => ['$hour' => '$TIMESTAMP'],
                                        'minute'   => ['$minute' => '$TIMESTAMP'],
                                        'second'   => ['$second' => '$TIMESTAMP'],
                                        'millisecond'   => ['$millisecond' => '$TIMESTAMP'],
                                            'value'   => '$BattV'

                                    ]
                                ],
                                [
                                    '$group' => [
                                        '_id'    => [
                                            'year'  => '$year',
                                            'month' => '$month',
                                            'day'   => '$day',
                                            'hour'  => '$hour',
                                            'minute'   => '$minute',
                                            'second' => '$second',
                                            'millisecond' => '$millisecond'

                                        ],
                                        'count'  => ['$sum' => 1],
                                        'values' => ['$addToSet' => '$value']
                                    ]
                                ],
                                 [
                                    '$sort' => ['_id' => 1]
                                ],
                                [
                                    '$project' => [
                                        'a' => '$count',
                                        'new' =>  ['$avg' => '$values'],
                                        'y' => [
                                            '$concat' => [
                                                ['$substr' => ['$_id.year', 0, 4]],
                                                '-',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.month', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.month', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.month', 0, 2]]
                                                    ]
                                                ],
                                                '-',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.day', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.day', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.day', 0, 2]]
                                                    ]
                                                ],
                                                ' ',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.hour', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.hour', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.hour', 0, 2]]
                                                    ]
                                                ],
                                                ':',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.minute', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.minute', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.minute', 0, 2]]
                                                    ]
                                                ],
                                                ':',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.second', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.second', 0, 2]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.second', 0, 2]]
                                                    ]
                                                ],
                                                '.',
                                                [
                                                    '$cond' => [
                                                        ['$lte' => ['$_id.millisecond', 9]],
                                                        [
                                                            '$concat' => [
                                                                '0',
                                                                ['$substr' => ['$_id.millisecond', 0, 1]],
                                                            ]
                                                        ],
                                                        ['$substr' => ['$_id.millisecond', 0, 1]]
                                                    ]
                                                ]

                                            ]
                                        ],
                                    ]
                                ]
                            ]
                        );