Question 90569 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    I don’t know typeorm. but based on https://github.com/typeorm/typeorm/issues/881

    The sql query would be like:

    WITH cte (
    id,
    name,
    bar
) AS (
    VALUES (1, 'whatever', ARRAY['apple', 'bananna', 'yeti', 'woo']),
        (2, 'something else', ARRAY['red', 'blue', 'green', 'boo']),
        (3, 'i dunno', ARRAY['ford', 'chevy'])
),
cte1 AS (
    SELECT
        json_agg(row_to_json(cte.*)) AS json_all
    FROM
        cte
)
SELECT
    value
FROM
    cte1,
    json_array_elements(json_all)
WHERE
    value ->> 'bar' ~ 'oo';

    Based on the github page, it would be like:

    getConnection().query("
with cte(id,name,bar) as (values
 (1,'whatever',array ['apple','bananna','yeti','woo'])
,(2,'something else',array  ['red','blue','green', 'boo'])
,(3,'i dunno',array ['ford','chevy'])
),cte1 AS
(select json_agg(row_to_json(cte.*)) as json_all from cte)
select value
from cte1,json_array_elements(json_all)
where value->>'bar' ~ @0", ['oo']);

    case insensitively match would be value->>'bar' ~* @0"

    Login or Signup to reply.
  2. 0

    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();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search