Steve Steve - 6 months ago 10
SQL Question

Multidimensional array output as JSON

I want to do something I'm not entirely sure is possible.

I 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 I want to get data grouped into an object with
common
values as keys:

{
"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"}
}


I can, of course, do it with multiple selects, but a single select would be beneficial.

Any help would be fantastic! Thanks!

Answer

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 an object 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.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

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, it is a tough call, but consider the following:

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.

Comments