I am trying to check if an array has any of elements which starts with or ends with the string specified.
For Comparing strings we could do something like below,
select * from table where 'gggggg' ilike '%g';
Can someone help to find out if an array contains values as like pattern.
Eg array : ['str1', 'str2', 'str3']
Also I want to find if any of elements ends with 1 or starts with 'str'.
For now, the only thing you can do is unnest the array and test each element:
create table test (a text[]);
insert into test values (array['abc', 'def', 'ghi']);
select distinct a from test
JOIN lateral (select * from unnest(a) as u) as sub on TRUE
WHERE u like '%g';
a
---
(0 rows)
select distinct a from test
JOIN lateral (select * from unnest(a) as u) as sub on TRUE
WHERE u like 'g%';
a
---------------
{abc,def,ghi}
(1 row)
In postgres 12, you will be able to use jsonb_path_exists. Of course, this would work better if you stored your data in jsonb, but it will still work, just not as efficiently:
-- Starts with g
select a from test
where jsonb_path_exists(to_jsonb(a), '$[*] ? (@ like_regex "^g")');
a
---------------
{abc,def,ghi}
(1 row)
-- Ends with g
select a from test
where jsonb_path_exists(to_jsonb(a), '$[*] ? (@ like_regex "g$")');
a
---
(0 rows)
