I have query.
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:
 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?
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.