How to check if list of values in one column match list of values in another column (aggregations)

Question

Given two results consisting of single key|value pair (you can imagine those are in CTEs), I want to join and group them by key, aggregate their values and return two different things:

a) those keys where aggregated list of values in first resultset exactly matches aggregated list of values in second resultset

b) those keys where aggregated list of values in first resultset matches second resultset independent of order

Actually I know something very close to this is string_agg, but It seems I can use this in select only and it's inefficient anyway. Is there something more efficient for this?

# resultset 1
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 3   | 4     |
| 2   | 5     |
| 2   | 7     |
| 1   | 3     |

# resultset 2
|key  | value |
|-----|-------|
| 1   | 1     |
| 1   | 2     |
| 1   | 3     |
| 2   | 7     |
| 2   | 5     |
| 4   | 6     |

Desired result

a) key 1

(1,2,3 = 1,2,3)

b) key 1 and key 2

(5,7 = 7,5)

Answer 1

For b) you can use intersection to find out what tuples that are in both resultsets, and then aggregate on top of that:

with rs1 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(3,4))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,3),(2,5),(2,7),(4,6))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect
    select key, value from rs2
) t
group by key;

1   {3,2,1}
2   {7,5}

If you are dealing with bags instead of sets, you can use intersect all to preserve duplicates:

with rs1 (key, value) as ( values (1,1),(1,2),(1,1),(4,6))
   , rs2 (key, value) as ( values (1,1),(1,2),(1,1),(5,7))
select key, array_agg(value)
from (
    select key, value from rs1
    intersect all
    select key, value from rs2
) t
group by key;

1   {2,1,1}

a) does not really make sense, since there is no order to take into concideration. We can create one by adding an ordering number n for each key

with rs1 (key, n, value) as ( values (1,1,1),(1,2,2),(1,3,3),(2,1,5),(2,2,7),(3,1,4))
   , rs2 (key, n, value) as ( values (1,1,1),(1,3,2),(1,2,3),(2,2,5),(2,1,7),(4,1,6))
select key, array_agg(value)
from (
    select key, n, value from rs1
    intersect
    select key, n, value from rs2
) t
group by key;

1   {1}

Another possible interpreation is that n is a total ordering (i.e. not within each key). The same solution can be used to deal with that.