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?

Ftisiot · Accepted Answer · 2022-08-09 10:01:40Z

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

answered Aug 9, 2022 at 10:01

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

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

1 Comment

juztcode Over a year ago

To anyone coming across this, I was actually string_agg(column) instead of array_agg, so my arrays actually had been coming out like this all along {"a, b"}, really wasted all that time, t

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related