Postgres加入unnest [英] Postgres JOIN with unnest

问题描述

假设我有以下表格:

table: followers_arrays

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


table: small_profiles

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

我想使用简单的JOIN使用来自 small_profiles 的填充数据打印 followers_array .

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

起初，我正在使用 unnest 这样的函数:

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

现在，据我所知，我只需要将此数据连接到 small_profiles.id 键上的 small_profiles 上，就像这样:

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

但是在JOIN期间似乎还没有创建列 elem ，因为出现以下错误: 错误:列"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

推荐答案

这是错误的设计，但这是您的答案:

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

这篇关于Postgres加入unnest的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！