Martti Mela Martti Mela - 11 months ago 54
PHP Question

mongodb: finding the highest numeric value of a column

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 Source

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);