Postgres aggregate results based on 2 columns

Question

In postgres 9.6 I have a table that includes these 3 columns.

id | val1 | val2
------------------
1  | x    | 1
1  | x    | 2
1  | x    | 3
1  | y    | 4
2  | y    | 1
2  | y    | 2

Can I use aggregate functions to turn it into this?

id | 
------------------------------
1  | { x: [1, 2, 3], y: [4] }
2  | { y: [1, 2] }

Or if what I'm looking for isn't possible, something like below could work too. Or anything that would allow me to transform the results into the above in my application code.

id | 
------------------------------
1  | [ {x: 1}, {x: 2}, {x: 3}, {y: 4} ]
2  | [ {y: 1}, {y: 2} ]

I know I can do something like select id, array_agg(val2) from mytable group by val2, but that only groups by val2 and returns something like

id | 
------------------
1  | [1, 2, 3, 4]
2  | [1, 2]

Did any of the answer solve your problem? You haven't commented on any. — joanolo
– joanolo, Commented Jul 15, 2017 at 23:05

joanolo · Accepted Answer · 2017-07-15 23:50:50Z

5

You can do it with:

SELECT
    id, json_object_agg(val1, aaa)
FROM
    (
    SELECT
        id, val1, json_agg(val2) AS aaa
    FROM
        t
    GROUP BY
        id, val1
    ) AS q 
GROUP BY
    id;

id | json_object_agg               
-: | :-----------------------------
 1 | { "x" : [1, 2, 3], "y" : [4] }
 2 | { "y" : [1, 2] }

You can find the needed definitions to produce this result at dbfiddle here

You'll be using these two functions:

json_agg
json_object_agg

The documentation can be found at 9.20. Aggregate Functions in PostgreSQL doc

edited Jul 15, 2017 at 23:50

answered Jul 1, 2017 at 14:24

joanolo

6,3881 gold badge33 silver badges40 bronze badges

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

Comments

Oto Shavadze · Accepted Answer · 2017-07-01 14:24:42Z

4

Here is one way using jsonb_agg and jsonb_object_agg functions

with the_table(id , val1 , val2) as(
    select 1 ,'x',1 union all
    select 1  ,'x', 2 union all
    select 1  ,'x', 3 union all
    select 1  ,'y', 4 union all
    select 2 ,'y', 1 union all
    select 2 ,'y', 2
)


select id, jsonb_object_agg(val1, arr) from (
    select id, val1, jsonb_agg(val2) as arr
    from the_table
    group by id, val1
)t
group by id

answered Jul 1, 2017 at 14:24

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

Comments

Abelisto · Accepted Answer · 2017-07-01 19:31:23Z

And yet another useful piece of code inspired by SO question:

drop aggregate if exists jsonb_objarr_agg(text, anyelement);
drop function if exists jsonb_objarr_agg_func(jsonb, text, anyelement);
create function jsonb_objarr_agg_func(jsonb, text, anyelement) returns jsonb immutable language sql as $$
  select
    jsonb_set(
      $1,
      array[$2],
      coalesce(
        case
          when jsonb_typeof($1->$2) <> 'array' then to_jsonb(array[$1->$2])
          else $1->$2
        end, 
        '[]'::jsonb) || to_jsonb($3), true);
$$;

select jsonb_objarr_agg_func('{"a":1}', 'a', 2);

create aggregate jsonb_objarr_agg(text, anyelement) (
  sfunc = jsonb_objarr_agg_func,
  stype = jsonb,
  initcond = '{}');

with t(i, v1, v2) as (values
    (1, 'x', 1),
    (1, 'x', 2),
    (1, 'x', 3),
    (1, 'y', 4),
    (2, 'y', 1),
    (2, 'y', 2))
select
  i,
  jsonb_objarr_agg(v1, v2)
from t
group by i;

╔═══╤════════════════════════════╗
║ i │      jsonb_objarr_agg      ║
╠═══╪════════════════════════════╣
║ 1 │ {"x": [1, 2, 3], "y": [4]} ║
║ 2 │ {"y": [1, 2]}              ║
╚═══╧════════════════════════════╝

This aggregate allows to make jsonb with first parameter as keys and also aggregate the second parameter into arrays.

If you need the json type instead then you can easily replace jsonb to json in this code.

live demo

Gordon Linoff · Accepted Answer · 2017-07-01 14:29:05Z

0

I think the following does what you want:

select id, json_agg(json_build_object(val1, val2s ))
from (select id, val1, array_agg(val2) as val2s
      from t
      group by id, val1
     ) t
group by id;

answered Jul 1, 2017 at 14:29

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

Postgres aggregate results based on 2 columns

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related