How do I join an ARRAY to a COLUMN?-CodePudding

I have two large tables - Table_A and Table_B - that I want to join on the ID field. "ID" in Table_A is a column and "IDs" in Table_B is an array

Table_A:

ID  | City       | 
---- ------------ 
101 | London     |     
102 | Paris      | 
103 | Rome       | 
104 | Copenhagen | 
105 | Amsterdam  | 
106 | Berlin     | 
107 | Cardiff    | 
108 | Lisbon     | 

Table_B:

Date  | Sessions | IDs
------ ---------- --------------
06-02 | 1        | [107,102]    
06-03 | 1        | [103]  
11-12 | 1        | [105,107,103]  
27-06 | 1        | [104,108]  
31-01 | 1        | [105]  
22-04 | 1        | [106,102]  
08-07 | 1        | [101,105,108]  
02-10 | 1        | [105]  

Desirable Output:

Date  | Sessions | ID          | City
------ ---------- ------------- -------------
06-02 | 1        | 107         | Cardiff
      |          | 102         | Paris
06-03 | 1        | 103         | Rome
11-12 | 1        | 105         | Amsterdam
      |          | 107         | Cardiff
      |          | 103         | Rome
27-06 | 1        | 104         | Copenhagen
      |          | 108         | Lisbon
            ...

I have tried using inner joins with unnest and union all but nothing is working. Any help would be appreciated.

CodePudding user response：

Something along those lines should yield the result you are looking for

  select 
    date, 
    sessions, 
    array_agg(id_un) as id, 
    array_agg(city) as city 
  from table_b b, unnest (id) as id_un
  left join table_a a on id_un = a.id
  group by 1, 2

CodePudding user response：

Consider also below approach

select date, sessions, ids as id, 
  array(
    select city
    from b.ids id
    left join Table_A
    using(id)
  ) city
from Table_B b

if applied to sample data in your question - output is