typeOrm脚本，用于基于jsonb元素过滤器选择项目

Question

我有一个有点复杂的SQL脚本，它工作得很好，但我有麻烦将它转移到TypeORM QueryBuilder语法，并将非常感谢任何帮助与此。

实体:

@Entity('users')
export class User {
@PrimaryColumn()
userId: string;
@Column("varchar")
email: string;
@Column("text", { array: true })
userCookieIds: string[];
@Column("varchar", { array: true })
userLocalIds: string[]
@Column("jsonb")
subscriptions: {retailerId: string, variantId: string, itemId: string, type: string}[]  
}

sql代码:

SELECT email
FROM users,jsonb_array_elements(subscriptions) with ordinality arr(item_object, position) 
WHERE arr.item_object->>'variantId' = '40100651171863' AND arr.item_object->>'type' = 'inStock'

Answer 1

.query()在这种情况下工作得很好

所以结果代码是:

let userEmails = await this.usersRepository
.query(`SELECT email FROM users,jsonb_array_elements(subscriptions) with ordinality arr(item_object, position) WHERE arr.item_object->>'variantId' = '40100651171863' AND arr.item_object->>'type' = 'inStock'`);