How do I query an array in TypeORM

Question

I want to create user permissions management. I use TypeORM with PostgreSQL. This is the column definition for the permissions within the user entity:

@Column({
  type: 'text',
  array: true
})
permissions: UserPermission[] = [];

This is the UserPermission enum:

export enum UserPermission {
  APP_USER = 'APP_USER',
  USER_ADMIN = 'USER_ADMIN',
  SUPERADMIN = 'SUPERADMIN'
}

I want to find one user who has the 'SUPERADMIN' permission but I cannot find the right spot in the documentation / github issues which explains how to do this. I already spent over an hour on this and I suppose this is a simple task.

Is there something like "Includes" to check if the permissions array includes a specific element and/or includes multiple elements?

const user = await this.userRepository.findOne({
  where: {
    permissions: Includes('SUPERADMIN')
  }
});

I would be very thankful if someone could point me to the correct documentation page :)

---

Edit:

The following works for me but I think it is not optimal yet:

@Column('simple-json')
permissions: string[];

let user = await this.userRepository.createQueryBuilder('user')
  .where('user.permissions like :permissions', { permissions: `%"${UserPermission.SUPERADMIN}"%` })
  .getOne();

Answer 1

As Mykhalio mentioned , you can use the PostgreSQL array functions to do the trick.

Adding few more pointers.

1. If you want to check users with both USER_ADMIN and SUPER_ADMIN permissions.

.where('user.permissions @> :permissions', { permissions:['USER_ADMIN', 'SUPER_ADMIN']})

2. If you want to check users with either USER_ADMIN or SUPER_ADMIN permissions.

.where('user.permissions && :permissions', { permissions: ['USER_ADMIN', 'SUPER_ADMIN']})