Spearfisher Spearfisher - 3 months ago 8
SQL Question

SQL - filter rows if the array contains a specific items

I have a table

letter_count
looking like this:

count letters
2 ["a","b"]
4 ["b","c"]
2 ["a"]
0 ["b"]
6 ["c"]
7 ["d"]


I'd like to be able to query only the rows containing a specific letters.

I have tried:

SELECT * FROM letter_counter WHERE letters IN ('["a"]')


But it fails.

What's the right syntax for this here?

Answer

try this select:

SELECT * FROM letter_counter 
WHERE(case when position('a' in array_to_string(letters,',')) > 0 then true else false end)