PostgreSQL+TypeORM：如何对字符串数组执行LIKE模糊查询？

后端基于PostgreSQL + TypeORM开发，定义了如下实体：

@Entity()
@Index(['name'], {unique: true})
export class Foo extends BaseEntity 
{
 @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;

  @Column('varchar', { array: true })
  bar: string[];
}

已完成name字段的模糊查询实现：

let qs = Foo.createQueryBuilder('foo');
qs.andWhere('foo.name ILIKE :name', {
    name: `%${name}%`
});

现需实现对bar字符串数组的模糊匹配：只要数组中任意元素符合%查询词%的匹配规则，就返回该记录。例如输入%oo%时，需返回以下数据集中的前两条记录：

[
  {id: 1, name: 'whatever', bar: ['apple','bananna','yeti','woo']},
  {id: 2, name: 'something else', bar: ['red','blue','green', 'boo']},
  {id: 3, name: 'i dunno', bar: ['ford','chevy']}
]

方案1：数组转字符串匹配（简单高效） #

利用PostgreSQL的array_to_string函数将数组拼接为字符串，再对拼接结果执行模糊匹配：

const searchTerm = 'oo'; // 用户输入的查询词
let qs = Foo.createQueryBuilder('foo');

// 可根据需求调整AND/OR逻辑，同时保留name字段的匹配
qs.andWhere('foo.name ILIKE :term', { term: `%${searchTerm}%` })
  .orWhere('array_to_string(foo.bar, ',') ILIKE :term', { term: `%${searchTerm}%` });

注意：若数组元素可能包含拼接用的分隔符（示例中为逗号），需更换为业务中不会出现的特殊字符，避免误匹配。

方案2：子查询+数组展开（精准可靠） #

通过unnest函数将数组展开为单行数据，再通过EXISTS子查询判断是否存在匹配元素：

const searchTerm = 'oo';
let qs = Foo.createQueryBuilder('foo');

qs.andWhere('foo.name ILIKE :term', { term: `%${searchTerm}%` })
  .orWhere(qb => {
    const subQuery = qb.subQuery()
      .select('1')
      .from(Foo, 'f')
      .where('f.id = foo.id')
      .andWhere('unnest(f.bar) ILIKE :term', { term: `%${searchTerm}%` })
      .getQuery();
    return `EXISTS(${subQuery})`;
  });

该方案不会因分隔符问题产生误匹配，逻辑更严谨。

方案3：简洁版子查询匹配 #

直接在OR条件中编写EXISTS子查询，代码更紧凑：

const searchTerm = 'oo';
let qs = Foo.createQueryBuilder('foo');

qs.orWhere('EXISTS (SELECT 1 FROM unnest(foo.bar) AS elem WHERE elem ILIKE :term)', {
  term: `%${searchTerm}%`
});

内容的提问来源于stack exchange，提问作者Yablargo