Query from a array and count the query result in Postgresql

Question

I have a keywordsList table has two columns, like this:

id | keywords 
-------------
1  | [apple cake, apple pie, apple cookie]
-------------
2  | [banana cake, banana juice]
-------------
3. | [orange candy]

I want to query the string which match anything in keywords columns and count how many result that I search successfully.
For example, I search "apple", and I want to get the result like this:

id | keywords                              | totalCount
-------------------------------------------------
1  | [apple cake, apple pie, apple cookie] |  1

I want to get the result shows that which array match "apple", and how many rows I got.
This's code that I try to get that result:

SELECT DISTINCT "id", "keywords", COUNT("id") OVER () as totalCount 
FROM (SELECT DISTINCT *, unnest("keywords") AS "unnestKeywords" FROM "keywordsList") AS "keywordsList" 
WHERE "unnestKeywords" ILIKE "%apple%"

But I get the result like this:

id | keywords                              | totalCount
-------------------------------------------------
1  | [apple cake, apple pie, apple cookie] |  3

I could get correct id and keywords columns, but couldn't get correct count.
Hope to get any suggestion. Thanks

I think you want to use DISTINCT in your COUNT COUNT(DISTINCT "id"), BTW can you make certain to post correct queries because I highly doubt "apple" is correct shouldn't that be '%apple%' — Eelke
– Eelke, Commented Jul 26, 2022 at 5:20
@Eelke Yes, I actually want to use DISTINCT in COUNT, but sql will throw error. Thanks for your remind, "apple" won't work. — jadokao
– jadokao, Commented Jul 26, 2022 at 5:58
It's unclear to me if you want to count the number of times a keyword occurs in a single array, or in all rows. What is the expected result if the search keyword is 'cake'? — user330315
– user330315, Commented Jul 26, 2022 at 6:09
@a_horse_with_no_name sorry for unclear. I want count the number of all rows. If search cake, I want get the result with 2 rows and count 2. — jadokao
– jadokao, Commented Jul 26, 2022 at 6:16

user330315 · Accepted Answer · 2022-07-26 06:26:23Z

1

You can do the counting in a scalar sub-select which removes the need of a GROUP BY. Then you can filter on that count, to get only those where the keyword occurs

select id, 
       keywords, 
       keyword_count, 
       sum(keyword_count) over () as totalcount
from (
   select kl.id, 
          kl.keywords,
          (select count(distinct word)
           from unnest(kl.keywords) as u(word)
           where u.word ilike '%apple%') as keyword_count
   from keywordlist kl
) t
where keyword_count > 0

Collectives™ on Stack Overflow

Query from a array and count the query result in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related