Subquery on SELECT with conditional WHERE clause after it

So I have three tables,

news :

id|title                                                      |body                                                                                                                                                                                                                                                           |publised_at        |deleted_at|created_at         |
--+-----------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------+-------------------+
 1|Tax are Increasing Again, But Why?                         |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|                   |          |2021-06-06 14:11:24|
 2|AwanKinton are Making Brand New University Named DragonBall|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|                   |          |2021-06-06 14:11:24|
 3|AwanKinton Released New Car that Can Fly                   |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|                   |          |2021-06-06 14:11:24|
 4|Indonesia National Team Are Not Gonna Make it to World Cup |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|2021-06-06 14:11:24|          |2021-06-06 14:11:24|
10|10 Penyebab Istri Sebel Sama Suami                         |Sebel aslkdjas askldjlsajd alskdjlkas alskdjsalk asldkjaslk asdlkjasld asdljasl asldjsalk alsdjals lajsdlkaj                                                                                                                                                   |                   |          |2021-06-06 14:34:08|

topics :

id|name         |created_at         |
--+-------------+-------------------+
 1|politics     |2021-06-06 18:43:13|
 2|economy      |2021-06-06 18:43:13|
 3|auto         |2021-06-06 18:43:13|
 4|education    |2021-06-06 18:43:13|
 5|tech         |2021-06-06 18:43:13|
 6|culture      |2021-06-06 18:43:13|
 7|money        |2021-06-06 18:43:13|
 8|sports       |2021-06-06 18:43:13|
 9|uncategorized|2021-06-06 18:43:13|

and news_topics :

id|news_id|topics_id|
--+-------+---------+
 1|      1|        7|
 2|      1|        4|
 3|      2|        3|
 4|      2|        4|
 5|      2|        5|
 6|      3|        3|
 7|      3|        4|
 8|      4|        8|

And I want to return it like these :

id|title                                                      |body                                                                                                                                                                                                                                                           |published_at|deleted_at|created_at         |t   |
--+-----------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+----------+-------------------+----+
 1|Tax are Increasing Again, But Why?                         |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|            |          |2021-06-06 18:43:13|    |
 2|AwanKinton are Making Brand New University Named DragonBall|Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|            |          |2021-06-06 18:43:13|auto|
 3|AwanKinton Released New Car that Can Fly                   |Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor i|            |          |2021-06-06 18:43:13|auto|

I succeed on returning that value using this query

select "news".*, 
(
    SELECT STRING_AGG("topics"."name", ',') as "topics"
    FROM "topics"
    INNER JOIN "news_topics" ON "topics"."id" = "news_topics"."topics_id"
    WHERE "news_topics"."news_id" = "news"."id"
    AND "topics"."name" IN ('auto')
) AS "topics" 
from "news" 
where published_at IS NULL AND deleted_at IS null 
order by "created_at" 
asc limit 10

But there is one flaw to that query, on the above result there are one row that contains null topics. When I add AND topics IS NOT NULL after it gave me error SQL Error [42703]: ERROR: column "topics" does not exist it is makes sense because the order of the query SQL executed.

I want rows that had null value on topics aren’t included on the query result. How do I get that?

Answer

Instead of using a correlated subquery to get the column topics, aggregate on the joined tables topics and news_topics and then join to news:

select n.*, t.topics 
from news n 
inner join (
  SELECT nt.news_id, STRING_AGG(t.name, ',') AS topics
  FROM topics t INNER JOIN news_topics nt
  ON t.id = nt.topics_id
  WHERE t.name IN ('auto')
  GROUP BY nt.news_id
) t ON t.news_id = n.id
where n.published_at IS NULL AND n.deleted_at IS null 
order by n.created_at asc limit 10

See the demo.