mongodb: finding the highest numeric value of a column

Question

I have MongoDB collection of documents containing several fields. One of the columns/fields should be numeric only, but some of these fields contain non-numerical (corrupt) data as string values. I should find the highest numerical value of this column, excluding the corrupt, non-numerical data. I am aware of the question Getting the highest value of a column in MongoDB, but AFAIK, this extended case was not covered.

The example below depicts the issue. For the highest value, the document with "age": 70 should be returned:

[
{
    "id": "aa001",
    "age": "90"
},
{
    "id": "bb002",
    "age": 70
},
{
    "id": "cc003",
    "age": 20,
}
]

Providing a PHP example for the find() / findOne() query would be of much help. Thanks a lot!

JohnnyHK came up with the perfect solution. Here's the working PHP code:

$cursor = $collection->find(array('age' => array('$not' => array('$type' => 2))), array('age' => 1));
$cursor->sort(array('age' => -1))->limit(1);

Answer 1

You can use the $type operator with $not in your query to exclude docs where age is a string. In the shell your query would look like:

db.test.find({age: {$not: {$type: 2}}}).sort({age: -1}).limit(1)

Or in PHP from Martti:

$cursor = $collection->find(array('age' => array('$not' => array('$type' => 2))), array('age' => 1));
$cursor->sort(array('price' => -1))->limit(1);

Answer 2

with PHP driver (mongodb)
using findOne()

$filter=[];
$options = ['sort' => ['age' => -1]]; // -1 is for DESC
$result = $collection->findOne(filter, $options);
$maxAge = $result['age']

Answer 3

You can use aggregate function to get maximum number from collections like this.

$data=$collection->aggregate(array
                    ( '$group'=> 
                        array('_id'=>'',
                            'age'=>array('$max'=>'$age'.)
                        )
                    )
            );

Answer 4

This works for me

$options = ['limit' => 100,'skip' => 0, 'projection' => ['score' => ['$meta' => 'textScore']], 'sort' => ['score' => ['$meta' => 'textScore']]];