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, Apr 25 '18 at 22:25

score 19 · Accepted Answer · answered Nov 01 '15 at 20:46

19

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 01 '15 at 20:46

Clodoaldo Neto

118,695
26
233
260

2

Works! Could you please tell me why it's bad design? – Rafal Wiliński Nov 01 '15 at 21:46
@Rafal There is no reason to have an array at that. Plain normalization would work much better. – Clodoaldo Neto Nov 01 '15 at 22:10
2

@ClodoaldoNeto Why would plain normalization work better? – Roshambo Sep 27 '17 at 01:35
7

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. – isapir Dec 28 '17 at 15:30

isapir · Answer 2 · 2018-07-12T20:41:12.557

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 );

CTE is not a substitute of sub-query. There are some performance concerns regarding using it - https://thoughtbot.com/blog/advanced-postgres-performance-tips#common-table-expressions-and-subqueries — Matzz, Dec 14 '20 at 11:47

score 3 · Answer 3 · answered Nov 09 '21 at 04:02

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.

Postgres JOIN with unnest

3 Answers3

Linked