53

The query below:

    SELECT  i_adgroup_id, i_category_id
    FROM adgroupcategories_br
    WHERE i_adgroup_id IN
    (
        SELECT i_adgroup_id
        FROM adgroupusers_br
        WHERE i_user_id = 103713
    )
    GROUP BY i_adgroup_id, i_category_id;

Gives me results like this:

    i_adgroup_id integer | i_category_id smallint
    ---------------------|-----------------------
    15938                | 2
    15938                | 3
    15938                | 4
    15942                | 1
    15942                | 2

What I want is results like this:

    i_adgroup_id integer | i_category_id smallint[]
    ---------------------|-----------------------
    15938                | { 2, 3, 4 }
    15942                | { 1, 2 }

How can I change the original SQL query to give me the result above?

DotNetDateQuestion
  • 803
  • 3
  • 8
  • 10

1 Answers1

81

You want to use array_agg, this should work:

SELECT i_adgroup_id, array_agg(i_category_id)
FROM adgroupcategories_br
WHERE i_adgroup_id IN
(
    SELECT i_adgroup_id
    FROM adgroupusers_br
    WHERE i_user_id = 103713
)
GROUP BY i_adgroup_id;

Note that i_category_id is no longer in the GROUP BY as it is now being aggregated.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • 1
    Thank you for your answer. Can we pass multiple columns into array_agg or any other aggregate function? I want to output multiple key:value pairs in the same output column with jsonb_object_agg() – Ulvi Jul 22 '21 at 17:28
  • 1
    @Ulvi You can `array_agg(array[c1, c2])` to build arrays of arrays or use derived tables (`select jsonb_object_agg(k, v) from (select k, array_agg(array[c1, c2]) v from ...) dt`). Or the existing JSON functions might do it without extra steps. Depends on what specifically you're trying to produce. – mu is too short Jul 22 '21 at 18:23