Postgres Array Prefix Matching

Question

I have an array search in Postgres hat matches at least one tag as this:

SELECT * FROM users WHERE tags && ['fun'];

| id | tags      |
| 1  | [fun,day] | 
| 2  | [fun,sun] |

It is possible to match on prefixes? Something like:

SELECT * FROM users WHERE tags LIKE 'f%';

| id | tags      |
| 1  | [fun,day] | 
| 2  | [fun,sun] |
| 3  | [far]     | 
| 4  | [fin]     |

Answer 1

try this

create table users (id serial primary key, tags text[]);

insert into users (tags)
values
  ('{"fun", "day"}'),
  ('{"fun", "sun"}'),
  ('{"test"}'),
  ('{"fin"}');

select *
from users
where exists (select * from unnest(tags) as arr where arr like 'f%')

SQL FIDDLE EXAMPLE

Answer 2

Here's a working example that should get you more or less what you're after. Note that I am not claiming that this approach will scale...

create table users (
id      serial primary key,
tags    text[] not null
);

insert into users (tags) values
('{"aaaa","bbbb","cccc"}'::text[]),
('{"badc","dddd","eeee"}'::text[]),
('{"gggg","ffbb","attt"}'::text[]);

select *
from (select id,unnest(tags) arr from users) u
where u.arr like 'a%';

 id | arr  
----+------
  1 | aaaa
  3 | attt