Unable to access column alias in WHERE clause

Question

I am trying to filter rows from my Postgres database with below query. Everything works fine but when I try to check if latesttask column is null or has some value then it shows error:

error: column "latesttask" does not exist

SELECT *, 
  (
    SELECT 
      JSON_BUILD_OBJECT(
        'id', taskhistories.id, 'task', taskhistories.task, 
        'taskname', t.name, 'project', taskhistories.project, 
        'projectname', p.name, 'started_at', 
        taskhistories.started_at, 'stopped_at', 
        taskhistories.stopped_at
      ) 
    FROM 
      tasks AS t, 
      projects AS p, 
      latesttasks, 
      taskhistories 
    WHERE 
      taskhistories.user = users.id 
      AND latesttasks.task = t.id 
      AND latesttasks.project = p.id 
      AND taskhistories.id = latesttasks.taskhistory 
      AND (
        LOWER(t.name) LIKE '%we%' 
        OR LOWER(p.name) LIKE '%we%'
      )
  ) as latestttask 
FROM 
  users 
WHERE 
  (
    latesttask IS NULL 
    AND (
      LOWER(name) LIKE '%we%' 
      OR LOWER(email) LIKE '%we%'
    ) 
    OR latesttask IS NOT NULL
  )

Answer 1

One "t" to many in your column alias latestttask.

But, more importantly, you cannot reference output column names in the WHERE clause anyway. There would have to be a column users.latesttask to make the WHERE clause work.

This would be a working equivalent with a LEFT JOIN to a LATERAL subquery:

SELECT *
FROM   users u
LEFT   JOIN LATERAL (
   SELECT json_build_object(
              'id', h.id, 'task', h.task, 
              'taskname', t.name, 'project', h.project, 
              'projectname', p.name, 'started_at', h.started_at,
              'stopped_at', h.stopped_at) AS latesttask 
   FROM   tasks         t
   JOIN   latesttasks   l ON l.task = t.id 
   JOIN   projects      p ON p.id = l.project
   JOIN   taskhistories h ON h.id = l.taskhistory 
   WHERE  h.user = u.id 
   AND   (lower(t.name) LIKE '%we%' 
       OR lower(p.name) LIKE '%we%')
   ) l ON true
WHERE  l.latesttask IS NOT NULL
   OR  lower(u.name) LIKE '%we%'
   OR  lower(u.email) LIKE '%we%';

Answer 2

Please try with...

Select * from (SELECT 
  *, 
  (
    SELECT 
      JSON_BUILD_OBJECT(
        'id', taskhistories.id, 'task', taskhistories.task, 
        'taskname', t.name, 'project', taskhistories.project, 
        'projectname', p.name, 'started_at', 
        taskhistories.started_at, 'stopped_at', 
        taskhistories.stopped_at
      ) 
    FROM 
      tasks AS t, 
      projects AS p, 
      latesttasks, 
      taskhistories 
    WHERE 
      taskhistories.user = users.id 
      AND latesttasks.task = t.id 
      AND latesttasks.project = p.id 
      AND taskhistories.id = latesttasks.taskhistory 
      AND (
        LOWER(t.name) LIKE '%we%' 
        OR LOWER(p.name) LIKE '%we%'
      )
  ) as latesttask 
FROM 
  users ) a 
WHERE 
  (
    latesttask IS NULL 
    AND (
      LOWER(name) LIKE '%we%' 
      OR LOWER(email) LIKE '%we%'
    ) 
    OR latesttask IS NOT NULL
  )