finding a given item value in an array of a given column

Question

I have a table like this:

mTable:

  |id     | text[] mVals        |  

  |1      | {"a,b"}             |
  |2      | {"a"}               |        
  |3      | {"b"}               |

I'd like a query to return both rows {a,b},{b} If I specify only b, but, it doesn't really return the row having atleast one of the specified values and only returns rows with the value specified.

I have tried this:

SELECT mVals
FROM mTable 
WHERE ARRAY['a'] && columnarray;  -- Returns only {'a'}  

Also tried:

SELECT mVals 
FROM mTable 
WHERE mVals && '{"a"}'; -- Returns only {'a'}  

Nothing seems to be working as it should. What could be the issue?

Answer 1

to me it looks is working as expected, recreating your case with

create table test(id int, mvals text[]);
insert into test values(1, '{a}');
insert into test values(2, '{a,b}');
insert into test values(3, '{b}');

A query similar to the 1st one you posted works

SELECT mVals
FROM test 
WHERE ARRAY['a'] && mvals;

Results

 mvals
-------
 {a}
 {a,b}
(2 rows)

and with b instead of a

 mvals
-------
 {a,b}
 {b}
(2 rows)

P.S.: you should probably use the contain operator @> to check if a value (or an array) is contained in another array