How to add where clause with unnest in sql query?

Question

I have a query like this -

select unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
     from news_article) where rowz=1;

this query is not working because of putting rowz=1 in the query ? How to do it if I want only that result where rowz= 1 after unnest.

When i do this -

select unnest(string_to_array(na.news_category_id, ','))::int rowz from news_article;

My table is -

Create table news_article(
id                          bigserial NOT NULL PRIMARY KEY,
news_headline               character varying(70) NOT NULL,
news_content_src            character varying(240) NOT NULL,
news_language_id            integer NOT NULL,
news_category_id            character varying(50) NOT NULL,
news_publisher_id           integer NOT NULL references news_publishers(pub_id),
news_date                   timestamp WITH TIME ZONE Default now()
);

Then it gives me this result -

rowz
1
2
1
3
2

Answer 1

This answers your question:

SELECT * FROM
  (SELECT unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
   FROM news_article) AS categories
WHERE rowz = 1;

The trick is that you unnest the array into a set of records which you then use as a sub-query.

The result, however, looks silly. Do you perhaps want all details of news articles which have a news_category_id = 1, possibly among other categories? In that case:

SELECT a.*
FROM news_article a
JOIN (SELECT id, unnest(string_to_array(news_article.news_category_id, ',')):: int rowz
      FROM news_article) AS c ON c.id = a.id
WHERE c.rowz = 1;