Postgresql - Select in Select Alias Problem

Question

I have query.

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1b3a39357a5fe028f57b9ac26d147a1d

SELECT users.id as user_ids,
       (SELECT
           ARRAY_AGG(DISTINCT CONCAT(user_has_bonuses.bonus_id)) as bonus_ids
        FROM user_has_bonuses
            WHERE user_has_bonuses.user_id = users.id) as BONUS_IDS,
       (SELECT
           ARRAY_AGG(DISTINCT CONCAT(bonuses.bonus_id))
        FROM bonuses
            WHERE bonuses.bonus_id IN (BONUS_IDS)
            ) AS bonusIds
FROM users;

I am getting below error:

[42703] ERROR: column "bonus_ids" does not exist Hint: Perhaps you meant to reference the column "bonuses.bonus_id".

How can I use correctly this query?

As such, the qery makes little sense - at least to me. What are you trying to do? What are the results that you expect? — GMB, Sep 17 '20 at 21:55
thank you. I have many linked queries linked to this query result: `SELECT ARRAY_AGG (DISTINCT CONCAT (user_has_bonuses.bonus_id)) as bonus_ids FROM user_has_bonuses WHERE user_has_bonuses.user_id = users.id` This query must be defined once. And I have to use it everywhere. — omero, Sep 17 '20 at 22:05
`CONCAT(user_has_bonuses.bonus_id)` can be simplified to `user_has_bonuses.bonus_id` — , Sep 17 '20 at 22:09
The second array_agg() doesn't make sense to me. How can that ever be different to the first array of bonus_ids? — , Sep 17 '20 at 22:13

score 1 · Accepted Answer · answered Sep 17 '20 at 22:14

I think you are looking for lateral joins. Your example is too contrieved to really make sense, but the logic is:

select u.id, ub.*, b.*
from users u
cross join lateral (
    select array_agg(distinct ub.bonus_id) bonus_id
    from user_has_bonus ub
    where ub.user_id = u.id
) ub
cross join lateral (
    select ...
    from bonuses b
    where b.bonus_id = any(ub.bonus_id)
) b

As opposed to when using inline subqueries, you can refer to columns from one subquery within the next one - as shown in the where clause of the second subquery.

Note that you want to use any() rather than in to check if a value belongs to an array.

Postgresql - Select in Select Alias Problem

1 Answers1