typeOrm script for selecting items based on jsonb elements filter

user_15028662 0
Viewed 78

I have a bit complicated SQL Script which works fine, but I have troubles with transferring it to TypeORM QueryBuilder syntax, and would really appreciate any help with this.

Entity:


@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-code:

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'
typeorm nestjs postgresql
1 Answers

.query() worked in this case perfectly

So the resulting code is:

 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'`);
user_150286620
Related