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

query step is 1- from ..... 2- where .... 3- group by .... 4- select ...... So when you use alias 'elem' after select didn't work in join — Mustafa ShazLy
– Mustafa ShazLy, Commented Apr 25, 2018 at 22:25

Clodoaldo Neto · Accepted Answer · 2015-11-01 20:46:34Z

21

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

answered Nov 1, 2015 at 20:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

4 Comments

Rafal Wiliński Over a year ago

Works! Could you please tell me why it's bad design?

Clodoaldo Neto Over a year ago

@Rafal There is no reason to have an array at that. Plain normalization would work much better.

Roshambo Over a year ago

@ClodoaldoNeto Why would plain normalization work better?

isapir Over a year ago

Plain Normalization will work more efficiently in most cases, but that doesn't mean that this is bad design or that there are no use cases for this.

Niels Andersen · Accepted Answer · 2021-11-09 04:02:41Z

7

The answers above are functionally correct. I would recommend something like this, it is more compact and simpler:

SELECT f.id, sp.username, sp.pic
FROM followers_arrays fa
     CROSS JOIN unnest(fa.array) f(id)
     JOIN small_profiles sp ON sp.id = f.id;

I generally recommend against using CTE as it can be a barrier for the query planner. This data set is so small that it does not matter, but a larger dataset could be problematic. I also try to avoid sub-queries unless it can reduce the cartesian row set.

answered Nov 9, 2021 at 4:02

Niels Andersen

1312 silver badges2 bronze badges

Comments

isapir · Accepted Answer · 2018-07-12 20:41:12Z

6

I prefer to use Common Table Expressions over Subqueries:

WITH unnested_arr_1 AS (
    SELECT unnest(ARRAY[1, 2, 3]) array_1_item
)
,unnested_arr_2 AS (
    SELECT unnest(ARRAY[2, 3, 4]) array_2_item
)
SELECT  *
FROM    unnested_arr_1 arr1
    FULL OUTER JOIN unnested_arr_2 arr2 ON arr1.array_1_item=arr2.array_2_item

produces:

array_1_item |array_2_item |
-------------|-------------|
1            |[NULL]       |
2            |2            |
3            |3            |
[NULL]       |4            |

If joining only unnested arrays, then the above query can be simplified as follows:

SELECT * 
FROM   unnest(
    ARRAY[1, 2, 3]
   ,ARRAY[2, 3, 4]
) as U(array_1_item , array_2_item );

edited Jul 12, 2018 at 20:41

answered Dec 28, 2017 at 15:57

isapir

24.1k16 gold badges127 silver badges123 bronze badges

1 Comment

Matzz Over a year ago

CTE is not a substitute of sub-query. There are some performance concerns regarding using it - thoughtbot.com/blog/…

Collectives™ on Stack Overflow

Postgres JOIN with unnest

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related