Sorting ipaddress

[ska...@cavirin.com]

Hi All,

Is there a way to query data  sorting ipaddress data field. i know we can do it in java. it would be nice if i can sort in query itself to get performance. 

Shashi

[Stephen Steneker]

On Saturday, 11 October 2014 08:59:27 UTC+11, ska...@cavirin.com wrote:

Is there a way to query data  sorting ipaddress data field. i know we can do it in java. it would be nice if i can sort in query itself to get performance. 

Hi Shashi,

How are you storing your IP addresses? If they are stored as a string you'll need to do some manipulation in order to get the expected sort order.

A common approach is to add a decimal representation of the IP address for sorting.

For example (skipping the calculation of the IP to decimal for simplicity):

var asDecimal = 3232235776; // 192.168.1.0
for (i = 1; i < 20; i++) {
    ipAddress = '192.168.1.' + i; asDecimal++;
    db.ips.insert({'ip': ipAddress, 'dec': asDecimal});
}

If you query and sort by IP address (as a string) the results would look like:

> db.ips.find({},{ip:1,_id:0}).sort({ip:1}).limit(3)
{
  "ip": "192.168.1.1"
}
{
  "ip": "192.168.1.10"
}
{
  "ip": "192.168.1.11"
}

Whereas the decimal representation will sort as expected:

> db.ips.find({},{ip:1,_id:0}).sort({dec:1}).limit(3)
{
  "ip": "192.168.1.1"
}
{
  "ip": "192.168.1.2"
}
{
  "ip": "192.168.1.3"
}

Regards,

Stephen