SQL - filter rows if the array contains a specific items

Question

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?

Serialised data in a database column is a very bad idea, at least in part for the very issue you're encountering now. If you have control over the database schema you should refactor it to use a dependant table for the letters. — GordonM, Aug 15 '16 at 08:42

score 2 · Accepted Answer · answered Aug 15 '16 at 08:50

2

try this select:

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

answered Aug 15 '16 at 08:50

Piotr Rogowski

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

Use this:

SELECT * FROM letter_counter WHERE letters @> ARRAY['a']::varchar[]

Or you could spend 5 seconds searching Stack Overflow and just use this answer.

edited May 23 '17 at 11:51

Community

answered Aug 15 '16 at 08:42

Tim Biegeleisen

2 Answers2