Search code examples
arrayspostgresqlsql-likeany

postgresql search text into array of text

i have a table t1 

id  |  names
----|-------------------------
1   |  {jully , alex , sarah}
2   |  {bety , cate , jenifer}
3   |  {adam , pit , joee}
4   |  {piter , mat , andy}

so, i need rows have at least one name that start with "a" the result i need is in the below

in row 1 : alex

in row 3 : adam

in row 4 : andy

id   |   names
-----|-------------------------
1    |  {jully , alex , sarah}
3    |  {adam , pit , joee}
4    |  {piter , mat , andy}

a query like it

select * from t1 where 'a' like% any t1.name
Solution 
  • select *
from (
    select id, unnest(names) as name
    from t
) s
where name like 'a%';
 id | name 
----+------
  1 | alex
  3 | adam
  4 | andy

    To have it aggregated:

    select id, array_agg(name)
from (
    select id, unnest(names) as name
    from t
) s
where name like 'a%'
group by id;
 id | array_agg 
----+-----------
  4 | {andy}
  1 | {alex}
  3 | {adam}