Multidimensional array output as JSON

For PostgreSQL of any version

The important part here is the correct SELECT that would group your data in such a way to make it easy to transform into your dictionary type:

SELECT * FROM events GROUP BY common, id ORDER BY common

It produces the following output:

2    100    "Something"
3    100    "Something"
4    100    "Something"
9    100    "Something"
5    200    "Something"
6    200    "Something"
7    200    "Something"
8    200    "Something"
10   200    "Something"
11   200    "Something"
12   200    "Something"
14   200    "Something"
16   200    "Something"
13   300    "Something"
15   300    "Something"
17   300    "Something"

At this point, no matter what language you use for your server-side code, transforming it into a dictionary with common as the keys becomes an easy task.

For example, in Node.js you can do it like this:

function transform(rows) {
    var key, obj, result = {};
    rows.forEach(function (r) {
        if (r.common !== key) {
            if (obj) {
                result[key] = obj;
            }
            obj = {};
            key = r.common;
        }
        obj[r.id] = r.location;
    });
    if (obj) {
        result[key] = obj;
    }
    return result;
}

For a complete example, using pg-promise:

db.query('SELECT * FROM events GROUP BY common, id ORDER BY common')
    .then(data=> {
        console.log(transform(data));
    })
    .catch(error=> {
        console.log(error);
    });

Outputs:

{
    '100': {
        '2': 'Something',
        '3': 'Something',
        '4': 'Something',
        '9': 'Something'
    },
    '200': {
        '5': 'Something',
        '6': 'Something',
        '7': 'Something',
        '8': 'Something',
        '10': 'Something',
        '11': 'Something',
        '12': 'Something',
        '14': 'Something',
        '16': 'Something'
    },
    '300': {
        '13': 'Something',
        '15': 'Something',
        '17': 'Something'
    }
}

For PostgreSQL 9.4 and later

With PostgreSQL 9.4 and later you can do it all in SQL, using function json_object_agg:

SELECT json_object_agg(common, j ORDER BY common)
FROM (SELECT common, json_object_agg(id, location order by id)
AS j FROM events GROUP BY common) t;

Performance Comparison

Comparing the performance of the standard vs the new approach isn't easy, but consider this:

We have a simple single select + a very simple and fast transformation, versus a dual select + dual aggregation on the server. Therefore, I wouldn't be surprised if the older solution outperformed the new one.