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
– GordonM, Commented Aug 15, 2016 at 8:42

Piotr Rogowski · Accepted Answer · 2016-08-15 08:50:54Z

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, 2016 at 8:50

Piotr Rogowski

3,90021 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:51:39Z

1

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.

CommunityBot

11 silver badge

answered Aug 15, 2016 at 8:42

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges