postgresql : self join with array

Question

My question is about forming Postgres SQL query for below use case

Approach#1

I have a table like below where I generate the same uuid across different types(a,b,c,d) like mapping different types.

+----+------+-------------+
| id | type | master_guid |
+----+------+-------------+
|  1 | a    | uuid-1      |
|  2 | a    | uuid-2      |
|  3 | a    | uuid-3      |
|  4 | a    | uuid-4      |
|  5 | a    | uuid-5      |
|  6 | b    | uuid-1      |
|  7 | b    | uuid-2      |
|  8 | b    | uuid-3      |
|  9 | b    | uuid-6      |
| 10 | c    | uuid-1      |
| 11 | c    | uuid-2      |
| 12 | c    | uuid-3      |
| 13 | c    | uuid-6      |
| 14 | c    | uuid-7      |
| 15 | d    | uuid-6      |
| 16 | d    | uuid-2      |
+----+------+-------------+

Approach#2

I have a created two tables for id to type and then id to master_guid, like below

table1:

+----+------+
| id | type |
+----+------+
|  1 | a    |
|  2 | a    |
|  3 | a    |
|  4 | a    |
|  5 | a    |
|  6 | b    |
|  7 | b    |
|  8 | b    |
|  9 | b    |
| 10 | c    |
| 11 | c    |
| 12 | c    |
| 13 | c    |
| 14 | c    |
| 15 | d    |
| 16 | d    |
+----+------+

table2

+----+-------------+
| id | master_guid |
+----+-------------+
|  1 | uuid-1      |
|  2 | uuid-2      |
|  3 | uuid-3      |
|  4 | uuid-4      |
|  5 | uuid-5      |
|  6 | uuid-1      |
|  7 | uuid-2      |
|  8 | uuid-3      |
|  9 | uuid-6      |
| 10 | uuid-1      |
| 11 | uuid-2      |
| 12 | uuid-3      |
| 13 | uuid-6      |
| 14 | uuid-7      |
| 15 | uuid-6      |
| 16 | uuid-2      |
+----+-------------+

I want to get output like below with both approaches:

+----+------+--------+------------+
| id | type |  uuid  | mapped_ids |
+----+------+--------+------------+
|  1 | a    | uuid-1 | [6,10]     |
|  2 | a    | uuid-2 | [7,11]     |
|  3 | a    | uuid-3 | [8,12]     |
|  4 | a    | uuid-4 | null       |
|  5 | a    | uuid-5 | null       |
+----+------+--------+------------+

I have tried self-joins with array_agg on ids and grouping based on uuid but not able to get the desired output.

Use below query to populate data:

Approach#1

insert into table1 values 
(1,'a','uuid-1'),
(2,'a','uuid-2'),
(3,'a','uuid-3'),
(4,'a','uuid-4'),
(5,'a','uuid-5'),
(6,'b','uuid-1'),
(7,'b','uuid-2'),
(8,'b','uuid-3'),
(9,'b','uuid-6'),
(10,'c','uuid-1'),
(11,'c','uuid-2'),
(12,'c','uuid-3'),
(13,'c','uuid-6'),
(14,'c','uuid-7'),
(15,'d','uuid-6'),
(16,'d','uuid-2')

Approach#2

insert into table1 values 
(1,'a'),
(2,'a'),
(3,'a'),
(4,'a'),
(5,'a'),
(6,'b'),
(7,'b'),
(8,'b'),
(9,'b'),
(10,'c'),
(11,'c'),
(12,'c'),
(13,'c'),
(14,'c'),
(15,'d'),
(16,'d')

insert into table2 values 
(1,'uuid-1'),
(2,'uuid-2'),
(3,'uuid-3'),
(4,'uuid-4'),
(5,'uuid-5'),
(6,'uuid-1'),
(7,'uuid-2'),
(8,'uuid-3'),
(9,'uuid-6'),
(10,'uuid-1'),
(11,'uuid-2'),
(12,'uuid-3'),
(13,'uuid-6'),
(14,'uuid-7'),
(15,'uuid-6'),
(16,'uuid-2')

Please do not change the context of a answered question. The answers will not fit to the question anymore. If you have a second question please open a new one :) Furthermore it is not clear to me what you want to show us with your "2nd approach" — S-Man, Dec 10 '18 at 13:51
yes noted, will take care next time. With 2nd approach I was normalizing it further and checking queries. — Learner, Dec 10 '18 at 15:17

S-Man · Answer 1 · 2018-12-10T13:04:55.123

demo: db<>fiddle

Using window function ARRAY_AGG allows you to aggregate your ids per groups (in your case the groups are your uuids)

SELECT 
    id, type, master_guid as uuid, 
    array_agg(id) OVER (PARTITION BY master_guid) as mapped_ids
FROM table1
ORDER BY id

Result:

| id | type |   uuid | mapped_ids |
|----|------|--------|------------|
|  1 |    a | uuid-1 |     10,6,1 |
|  2 |    a | uuid-2 |  16,2,7,11 |
|  3 |    a | uuid-3 |     8,3,12 |
|  4 |    a | uuid-4 |          4 |
|  5 |    a | uuid-5 |          5 |
|  6 |    b | uuid-1 |     10,6,1 |
|  7 |    b | uuid-2 |  16,2,7,11 |
|  8 |    b | uuid-3 |     8,3,12 |
|  9 |    b | uuid-6 |    15,13,9 |
| 10 |    c | uuid-1 |     10,6,1 |
| 11 |    c | uuid-2 |  16,2,7,11 |
| 12 |    c | uuid-3 |     8,3,12 |
| 13 |    c | uuid-6 |    15,13,9 |
| 14 |    c | uuid-7 |         14 |
| 15 |    d | uuid-6 |    15,13,9 |
| 16 |    d | uuid-2 |  16,2,7,11 |

These arrays currently contain also the id of the current row (mapped_ids of id = 1 contains the 1). This can be corrected by remove this element with array_remove:

SELECT 
    id, type, master_guid as uuid,  
    array_remove(array_agg(id) OVER (PARTITION BY master_guid), id) as mapped_ids
FROM table1
ORDER BY id

Result:

| id | type |   uuid | mapped_ids |
|----|------|--------|------------|
|  1 |    a | uuid-1 |       10,6 |
|  2 |    a | uuid-2 |    16,7,11 |
|  3 |    a | uuid-3 |       8,12 |
|  4 |    a | uuid-4 |            |
|  5 |    a | uuid-5 |            |
|  6 |    b | uuid-1 |       10,1 |
|  7 |    b | uuid-2 |    16,2,11 |
|  8 |    b | uuid-3 |       3,12 |
|  9 |    b | uuid-6 |      15,13 |
| 10 |    c | uuid-1 |        6,1 |
| 11 |    c | uuid-2 |     16,2,7 |
| 12 |    c | uuid-3 |        8,3 |
| 13 |    c | uuid-6 |       15,9 |
| 14 |    c | uuid-7 |            |
| 15 |    d | uuid-6 |       13,9 |
| 16 |    d | uuid-2 |     2,7,11 |

Now for example id=4 contains an empty array instead of a NULL value. This can be achieved by using the NULLIF function. This gives NULL if both parameters are equal, else it gives out the first parameter.

SELECT 
    id, type, master_guid as uuid,  
    NULLIF(
        array_remove(array_agg(id) OVER (PARTITION BY master_guid), id), 
        '{}'::int[]
    ) as mapped_ids 
FROM table1
ORDER BY id

Result:

| id | type |   uuid | mapped_ids |
|----|------|--------|------------|
|  1 |    a | uuid-1 |       10,6 |
|  2 |    a | uuid-2 |    16,7,11 |
|  3 |    a | uuid-3 |       8,12 |
|  4 |    a | uuid-4 |     (null) |
|  5 |    a | uuid-5 |     (null) |
|  6 |    b | uuid-1 |       10,1 |
|  7 |    b | uuid-2 |    16,2,11 |
|  8 |    b | uuid-3 |       3,12 |
|  9 |    b | uuid-6 |      15,13 |
| 10 |    c | uuid-1 |        6,1 |
| 11 |    c | uuid-2 |     16,2,7 |
| 12 |    c | uuid-3 |        8,3 |
| 13 |    c | uuid-6 |       15,9 |
| 14 |    c | uuid-7 |     (null) |
| 15 |    d | uuid-6 |       13,9 |
| 16 |    d | uuid-2 |     2,7,11 |

very well explained! just one query I am trying to filter out records using type and in that case it does not return desired results. I just added where clause type='a' — Learner, Dec 10 '18 at 13:28
Just adding a WHERE clause will not work because the window function is calculated AFTER the filtering. So it will not find the uuids for filted types. You simply add the WHERE clause in a subquery: https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=5463a3265353cdd22b21ffe291065a65 — S-Man, Dec 10 '18 at 13:44

Hambone · Accepted Answer · 2018-12-10T13:49:18.950

0

Try this:

select
  t1.id, t1.type, t1.master_guid, array_agg (distinct t2.id)
from
  table1 t1
  left join table1 t2 on
    t1.master_guid = t2.master_guid and
    t1.id != t2.id
group by
  t1.id, t1.type, t1.master_guid

I don't come up with exactly the same results you listed, but I thought it was close enought that maybe there was a mistaken expectation on your side or only a small error on mine... either way, a potential starting point.

-- EDIT --

For approach #2, I think you just need to add an inner join to Table2 to get the GUID:

select
  t1.id, t1.type, t2.master_guid,
  array_agg (t2a.id)
from
  table1 t1
  join table2 t2 on t1.id = t2.id
  left join table2 t2a on
    t2.master_guid = t2a.master_guid and
    t2a.id != t1.id
where
  t1.type = 'a'
group by
  t1.id, t1.type, t2.master_guid

edited Dec 10 '18 at 13:49

answered Dec 10 '18 at 12:53

Hambone

15,600
8
46
69

Thanks! this seems to be working. I added the where clause for type and I could filter it. However on the same lines, if I remove master_guid column from table1 and create new table2 with id and master_guid column. How the query will look like then? – Learner Dec 10 '18 at 13:19
@Learner -- can you add the new table structure with some sample data to your question? You did a really nice job of framing the question and providing sample data, by the way. – Hambone Dec 10 '18 at 13:25
yes, this is working as expected for both the approaches. Thanks a ton !! – Learner Dec 10 '18 at 15:14

postgresql : self join with array

2 Answers2