Change Postgres select with multiple array_agg and group by

Question

I write SQL in postgres 9.3 which works almost perfectly:

SELECT type_id, to_json(array_agg(row(value, id))) AS json FROM sub_types GROUP BY type_id

The result table looks:

type_id | json
1       | [{"f1":"something", "f2":7}, ...]
2       | [{"f1":"something new", "f2":2}, ...]

I am trying to do that the result looks like:

type_id | json
1       | [{"value":"something", "id":7}, ...]
2       | [{"value":"something new", "id":2}, ...]

Basic idea is to to write code (PHP) something close to this: rows = pdo_call_select

rows = pdo_call_select
foreach (rows as row)
{
  print '<span data-id="row->id">'
  foreach (row->json as otherfields)
    print '<input value="otherfields->value" ...'
    ...

and my table is:

id | type_id | value
1      3         something
2      2         blabla
3      3         something new
4      1         ok
...

Clodoaldo Neto · Accepted Answer · 2014-07-26 19:41:11Z

2

create table sub_types (
    id int, type_id int, value text
);
insert into sub_types (id, type_id, value) values
(1, 3, 'something'),
(2, 2, 'blabla'),
(3, 3, 'something new'),
(4, 1, 'ok');

select type_id, json_agg(row_to_json(cj)) as json
from
    sub_types st
    cross join lateral
    (select value, id) cj
group by type_id
;
 type_id |                               json                               
---------+------------------------------------------------------------------
       1 | [{"value":"ok","id":4}]
       3 | [{"value":"something","id":1}, {"value":"something new","id":3}]
       2 | [{"value":"blabla","id":2}]

answered Jul 26, 2014 at 19:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

bhirt · Accepted Answer · 2014-07-26 22:36:55Z

0

I create types for all my json results and cast the rows to the type.

create table sub_types (
    id int, type_id int, value text
);

create type sub_json_type as (value text, id integer);

insert into sub_types (id, type_id, value) values
(1, 3, 'something'),
(2, 2, 'blabla'),
(3, 3, 'something new'),
(4, 1, 'ok');


SELECT type_id, to_json(array_agg(row(value, id)::sub_json_type)) AS json FROM sub_types GROUP BY type_id;

 type_id |                              json                               
---------+-----------------------------------------------------------------
       1 | [{"value":"ok","id":4}]
       2 | [{"value":"blabla","id":2}]
       3 | [{"value":"something","id":1},{"value":"something new","id":3}]
(3 rows)

edited Jul 26, 2014 at 22:36

answered Jul 26, 2014 at 22:25

bhirt

2662 silver badges8 bronze badges

Collectives™ on Stack Overflow

Change Postgres select with multiple array_agg and group by

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related