CODEWITHSUNDEEP
sqlpostgresql
Makla
Makla

Reputation: 10459

Change Postgres select with multiple array_agg and group by

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

Upvotes: 2

Views: 2390

Answers (2)

bhirt
bhirt

Reputation: 266

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)

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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}]

Upvotes: 2

Related Questions