postgresql search text into array of text

Question

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

Clodoaldo Neto · Accepted Answer · 2017-03-04 13:31:32Z

6

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}

answered Mar 4, 2017 at 13:31

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

Mehdi Monzavi Over a year ago

unnest function, break each row to separate row that any row contain ow item , it means we have 12 records instead of 4 record , with extra filed "name" , it is not a solution , in our pivot table, we have millions of record

Mehdi Monzavi Over a year ago

thanks clodoaldo , i tried it, as i guest... . thus i changed the arrays item into sapparate of item, that i sure they are unique (such as "andy" => "an" , "dy" ) , now i use array contain operand.... so by this approach my problem was sold... my experience ;-)

Abelisto · Accepted Answer · 2017-03-04 14:52:13Z

2

And yet another solution using unnest

select * from t1
where exists (
  select * from unnest(t1.names) n
  where n like 'a%')

answered Mar 4, 2017 at 14:52

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Comments

Kundan Sharma · Accepted Answer · 2019-08-23 05:23:31Z

2

If you have to search multiple values inside the text array. You can use this:

SELECT * FROM t1 WHERE names && ARRAY['alex', 'jully'] ;

answered Aug 23, 2019 at 5:23

Kundan Sharma

737 bronze badges

Collectives™ on Stack Overflow

postgresql search text into array of text

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related