PostgreSQL / TypeORM: String array type, how to use LIKE in query?

Question

My backend database is PostgreSQL I have a TypeORM object simplified to:

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


  @Column()
  name: string;

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

I'm creating an API query handler that can handle searches. I can easily do a LIKE query on the name like this:

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

I'd like to also search for e.g. any "bar" LIKE %myqueryterm% but I can't seem to find anything on that.

I see a bunch of docs on how to exactly match a search term in bar, but no soft comparison.

What I essentially want to do is that I have a data set

[
{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']},
]

and I'd like to let the user to be able to query e.g. "%oo% and return the first 2 records based on bar strings containing that substring.

Answer 1

Postgres provides array functions and operators that you can use to create any complex query.

In your case, a clean way of doing this would be to

1. Convert the array to a string and then
2. Perform the LIKE operation on that string

Something like this should work:

.createQueryBuilder('foo')
.where("array_to_string(foo.bar, ',') LIKE :bar", {
 bar: '%aa%',
})
.getMany();