Get sql query result as json

Question

I have a table test with two columns A and B and create table1 and table2 from it.

 test          table1              table2
A   B         A  count(A)       B  count(B)   A
95  1         95   7            1     3       95
 5  11         5   2            11    2        5
95  1                           9     4       95
95  9
95  1
95  9
 5  11
95  9
95  9

How to get a result like:

{"node": [   
    {"child": [
              {"value": 3,
               "name": "1"},
              {"value": 4,
               "name": "9"}],
       "value": 7,
       "name": "95"},
   {"child": [
             {"value": 2,
              "name": "11"}],
      "value": 2,
      "name": "5"}],
 "name": "test",
 "value": 9}

First I group by column A and count the groups name="95", value=7 and name="5", value=2. For each group I count also column B. There are alot of json functions, but till now I have no idea how to get the result above.

finely the query should be similar to:

select row_to_json(t) from ( select * , ( select array_to_json(array_agg(row_to_json(u))) from ( select * from table1 where table1.a=table2.a ) as u ) from table2 ) as t;

your json really doesn't make any sense according to your tables, can you explain a bit more as to how you want to get the names and values? — Jeremy C., Jun 09 '15 at 11:04
I edit my start post, what I did is I count over A and want to save the number of "95" in the variable value=7. Then I I count for both groups ("95" and "5") over B. — StellaMaris, Jun 09 '15 at 11:34
http://sqlfiddle.com/#!9/568307/3 this is how you get the data from your database but idk how to put it in that format without using an application layer — Jeremy C., Jun 09 '15 at 11:48
I also know how to count over column A and B. And I guess the json format I need is possible, something similar to (http://stackoverflow.com/questions/21137237/postgres-nested-json-array-using-row-to-json) — StellaMaris, Jun 09 '15 at 12:16

score 1 · Accepted Answer · answered Jun 09 '15 at 13:24

You can generate the correct json with a plpgsql function. This is not very difficult, although sometimes a little tedious. Check this one (rename tt to actual table name):

create or replace function test_to_json()
returns json language plpgsql
as $$
declare
    rec1 record;
    rec2 record;
    res text;
begin
    res = '{"node": [';
    for rec1 in
        select a, count(b) ct
        from tt
        group by 1
    loop
        res = format('%s{"child": [', res);
        for rec2 in
            select a, b, count(b) ct
            from tt
            where a = rec1.a
            group by 1,2
        loop
            res = res || format('{"value": %s, "name": %s},', rec2.ct, rec2.b);
        end loop;
        res = rtrim(res, ',');
        res = format('%s],"value": %s, "name": %s},', res, rec1.ct, rec1.a);
    end loop;
    res = rtrim(res, ',');
    res = format('%s], "value": %s}', res, (select count(b) from tt));
    return res:: json;
end $$;

select test_to_json();

There should be an easier solution. Suppose we have these two tables (http://sqlfiddle.com/#!9/568307/3). Then it seems possible to produce the output that I need via the functions row_to_json and array_to_json if I understand the following links (http://stackoverflow.com/questions/21137237/postgres-nested-json-array-using-row-to-json) and (http://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins) — StellaMaris, Jun 09 '15 at 14:03
Something similar to ' select row_to_json(t) from ( select * , ( select array_to_json(array_agg(row_to_json(u))) from ( select * from table1 where table1.a=table2.a ) as u ) from table2 ) as t;' — StellaMaris, Jun 09 '15 at 14:17

score 1 · Answer 2 · answered Jun 10 '15 at 20:33

1

Ugly but working and without plpgsql:

select json_build_object('node', json_agg(q3), 'name', 'test', 'value', (select count(1) from test))
from
(select json_agg(q2) from 
    (select a as name, sum(value) as value, json_agg(json_build_object('name', q1.name, 'value', q1.value)) as child 
    from
        (select a, b as name, count(1) as value from test group by 1, 2) as q1
    group by 1) as q2
) as q3;

answered Jun 10 '15 at 20:33

Radek Postołowicz

4,506
2
30
47

And which way is easier? Congratulations :) – klin Jun 10 '15 at 21:05

Get sql query result as json

2 Answers2