Sorting a collection by array value

[pihentagy]

Continuing the discussion from Aldeed tabular sort the formatted number:

Aldeed tabular sort the formatted number

So maybe you need Mongo to do the sorting as @marczzio12 suggests. So, save the IP address in Mongo as an 8-byte hex string and use the render function to reformat into standard octet form:

Encoding (IP to hex):

const ipToHex = function(ip) {
  return ip.split('.').map(octet => {
    return (+octet).toString(16).replace(/^(.)$/, '0$1');
  }).join('')
}

Decoding (val is the hex encoded IP string):

render: function(val, type, doc) {
  return val.match(/(..?)/g).map(pair => {
    return parseInt(pair,16);
  }).join('.');
}

Natural sorting will work fine using this technique, but you should apply an index to the MongoDb field you are storing the encoded IP into.

So, breaking the topic from tabular, is it considered best practice to store arrays (like IP address) in mongodb as scalars, if I need to sort by that value, and apply transformation, or collection helper to get the nice representation? I though it is a good idea to store an IP address as an array, like: [3,249,10,44]

[robfallows]

@pihentagy

is it considered best practice to store arrays (like IP address) in mongodb as scalars

I would say, generally speaking, no.

Having said that, I’ve never considered an IP address as an array and converting to a single integer is common storage practice (aaa.bbb.ccc.ddd takes 15 bytes for what can be stored unambiguously as a 4 byte unsigned integer. Converting to 8 bytes of hex takes more space than the smallest (string formatted) IPv4 address (a.b.c.d), but has the advantage that it’s easily parsable by eye, should that be useful, while still being natively sortable.

As far as the general argument for storing as an array in MongoDB. If these are to be sortable on large collections, they will need to be indexed. In MongoDB that means an index key for each element: https://docs.mongodb.org/manual/core/index-multikey/, which sounds expensive (in terms of space) for large collections and/or large arrays.

I’d be interested to know what others think.

[serkandurusoy]

In light of mongodb’s document oriented structure and encouragement of denormalization, I believe it is best to create separate sort fields which store either a number or a string, when sorted, represents the order of the fields you want as sorted.

A trivial example would be:

MyCollection.find({}, {sort: {order: 1}}).fetch();
// resulting in
[
  { content: "One", order: 1 },
  { content: "Two", order: 2 },
  { content: "Three", order: 3 },
  { content: "Four", order: 4 }
]