Postgres与unnest连接

Question

假设我有以下表格：

table: followers_arrays

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


table: small_profiles

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

我想使用简单的联接打印followers_array，其中包含来自small_profiles的填充数据。

首先，我使用unnest函数，如下所示：

SELECT id, unnest(followers_array) AS elem FROM followers_arrays 

它给了我正确的结果：

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

现在，根据我的理解，我只需要将这些数据连接到small_profiles ON small_profiles.id键，如下所示：

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

但是，似乎在联接期间，还没有创建列elem，因为我得到了以下错误: error:列"elem“不存在

有什么想法，我应该如何重新安排我的查询？谢谢

Answer 1

这是一个糟糕的设计，但这是你的答案：

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

Answer 2

我更喜欢在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

生产：

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

如果只连接非嵌套数组，则上述查询可以简化如下：

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

Answer 3

上述答案在功能上是正确的。我推荐这样的东西，它更紧凑，更简单：

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;

我通常建议不要使用CTE，因为它可能是查询计划器的一个障碍。这个数据集太小了，所以它并不重要，但是更大的数据集可能会有问题。我还试图避免子查询，除非它可以减少笛卡尔行集。