Steve Steve - 6 months ago 16
SQL Question

Multidimensional array (or JSON) output from postgresql

I am wanting to do something I'm not entirely sure is possible. What I want is to start with a database similar to this:

create table events (id int primary key, common int, location text);

insert into events values (2, 100, 'Something');
insert into events values (3, 100, 'Something');
insert into events values (4, 100, 'Something');
insert into events values (5, 200, 'Something');
insert into events values (6, 200, 'Something');
insert into events values (7, 200, 'Something');
insert into events values (8, 200, 'Something');
insert into events values (9, 100, 'Something');
insert into events values (10, 200, 'Something');
insert into events values (11, 200, 'Something');
insert into events values (12, 200, 'Something');
insert into events values (13, 300, 'Something');
insert into events values (14, 200, 'Something');
insert into events values (15, 300, 'Something');
insert into events values (16, 200, 'Something');
insert into events values (17, 300, 'Something');


and do some kind of select statement that will give me and output grouped by the common row. So for example an output similar to this:

[{
"100":{
"2":"Something",
"3":"Something",
"4":"Something",
"9":"Something"},
"200":{
"5":"Something",
"6":"Something",
"7":"Something",
"8":"Something"},
"300":{
"13":"Something",
"15":"Something",
"17":"Something",
"18":"Something"}
}]


Obviously I can do multiple selects here over every common value, but doing it in one would be beneficial.

Any help would be fantastic! Thanks!

Answer

For PostgreSQL of any version

The important part of such transformation is the correct SELECT that would group your data in such a way that's easy to convert into your type of objects.

Such query is this one:

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 an array of objects where common is the key becomes an easy task.

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

function transform(data) {
    var key, obj, result = [];
    data.forEach(function (d) {
        if (d.common === key) {
            obj[d.id] = d.location;
        } else {
            if (obj) {
                var val = {};
                val[key] = obj;
                result.push(val);
            }
            obj = {};
            obj[d.id] = d.location;
            key = d.common;
        }
    });
    if (obj) {
        var val = {};
        val[key] = obj;
        result.push(val);
    }
    return result;
}

For a complete example, using pg-promise:

db.any('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

If you are using PostgreSQL 9.4 or later, then 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;