sql - Postgres 数组前缀匹配

Question

我在 Postgres 中有一个数组搜索匹配至少一个标签，如下所示:

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

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

可以匹配前缀吗？像这样的东西:

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

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

Answer 1

试试这个

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