How do I join an ARRAY to a COLUMN?

Question

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.

Show what you tried

drum
– drum

2022-11-18 15:43:11 +00:00
Commented Nov 18, 2022 at 15:43 — drum
– drum, Commented Nov 18, 2022 at 15:43

Xavier Hamida · Accepted Answer · 2022-11-18 16:24:11Z

1

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

answered Nov 18, 2022 at 16:24

Xavier Hamida

1941 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cerinor Over a year ago

Thank you Xavier, however I'm getting a "No matching signature for operator = for argument types: STRING, INT64. Supported signature: ANY = ANY at..." when I attempted this. How can I get around this?

Xavier Hamida Over a year ago

Hmm can you try: left join table_a a on id_un = cast(a.id as string)

Mikhail Berlyant · Accepted Answer · 2022-11-18 21:24:15Z

1

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

answered Nov 18, 2022 at 21:24

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

7 Comments

Cerinor Over a year ago

I'm getting the error "Column id in USING clause not found on right side of join at ...". What should I do when this appears?

Mikhail Berlyant Over a year ago

make sure you have 'id' column in table_a as it is presented in your sample data

Cerinor Over a year ago

I've done that, but it's still returning the same error message

Mikhail Berlyant Over a year ago

as you can see - i was able produce expected result using your sample data! so the only i can suggest is to carefully check the query you ended up using, most likely you did some adjustment, etc.

Cerinor Over a year ago

Also, in my actual data, the id column in table a and array column in table b have the same column name, would I need to do anything different because of this?

|

Collectives™ on Stack Overflow

How do I join an ARRAY to a COLUMN?

2 Answers 2

2 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related