45

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
  • 703
  • 3
  • 7
  • 10

1 Answers1

71

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
  • 413,090
  • 67
  • 810
  • 771
  • 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