Postgres JOIN with unnest

Question

Assume I have following tables:

table: followers_arrays

   id   |  array
--------+---------
    1   | {3,4,5}


table: small_profiles

   id   | username |  pic    
--------+----------+-------
    3   |   aaaa   | abcd
    4   |   bbbb   | abcd
    5   |   cccc   | abcd

I would like to print followers_array with populated data from small_profiles using simple JOINs.

At first, I'm using unnest function like this:

SELECT id, unnest(followers_array) AS elem FROM followers_arrays 

And it gives me about right result:

   id   |  elem  
--------+--------
    1   |    3
    1   |    4
    1   |    5

Now, from my understanding I just need to join this data to small_profiles ON small_profiles.id key like this:

SELECT id, unnest(followers_array) AS elem 
FROM followers_arrays 
JOIN small_profiles ON small_profiles.instagram_id = elem

However it seems that during JOIN, column elem is not created yet because I get following error: ERROR: column "elem" does not exist

Any thoughts how should I rearrange my query? Thanks

Answer 1

That is bad design but here is your answer:

select f.id, f.follower, s.username, s.pic
from
    (
        select id, unnest("array") as follower
        from followers_arrays
    ) f
    inner join
    small_profiles s on f.follower = s.id