PostgreSQL - text Array contains value similar to -

i'm trying rows column of type text[] contains value similar user input.

what i've thought , done far use 'any' , 'like' operator this:

select * sometable '%someinput%' any(somecolum); 

but doesn't work. query returns same values query:

select * sometable 'someinput' = any(somecolum); 

i've got result using unnest() function in subquery need query in clause if possible.

why doesn't like operator work any operator , don't errors? thought 1 reason should any operator in right-hand of query, ...

is there solution without using unnest() , if possible in where clause?

it's important understand any not operator sql construct can used right of operator. more:

• how use instead of in in clause rails?

the like operator - or more precisely: expression, rewritten ~~ operator in postgres internally - expects value left , pattern right. there no commutator operator (like there simple equality operator =) postgres cannot flip operands around.

your attempt:

select * sometable '%someinput%' any(somecolum); 

has flipped left , right operand '%someinput%' value , elements of array column somecolum taken patterns (which not want).

it would have any(somecolum) '%someinput%' - except that's not possible any construct allowed right of operator. hitting road block here.

related:

• is there way usefully index text column containing regex patterns?
• can postgresql index array columns?

unnest() solution, found - or save elements instead of array begin (normalize schema).