PostgreSQL / TypeORM:字符串数组类型，如何在查询中使用LIKE ?

Question

我的后台数据库是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[];
}

我正在创建一个可以处理搜索的API查询处理程序。我可以像这样轻松地对名称执行LIKE查询:

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

我还想搜索例如any "bar"喜欢%myqueryterm%，但我似乎找不到任何相关内容。

我看到了一堆关于如何准确匹配搜索词的文档，但没有软比较。

我实际上想做的是我有一个数据集

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

和我想让用户能够查询例如"%oo%，并返回基于包含该子字符串的栏字符串的前2条记录。

Answer 1

Postgres提供了数组函数和操作符，可以用来创建任何复杂的查询。

在你的情况下，一个干净的方法是

1. 将数组转换为字符串，然后
2. 对该字符串执行LIKE操作

应该这样做:

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

Answer 2

我不知道typeform。但是基于https://github.com/typeorm/typeorm/issues/881

sql查询应该是这样的:

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';

基于github页面，它应该是这样的:

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']);

大小写不敏感匹配将是value->>'bar' ~* @0"