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();

I dont know if theres a way to use Include function, in the edit fragment I suggest you to use Like() function, to prevent SQL Injections: { permissions: Like(`%${UserPermission.SUPERADMIN}%`).value}). Anyway would not be better to store user permissons in a separated table and then make the relationship? — Nestor Perez, Dec 05 '19 at 18:20

score 14 · Answer 1 · answered Jul 11 '20 at 18:03

14

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

Adding few more pointers.

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

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

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

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

answered Jul 11 '20 at 18:03

Shijo Joseph

162
1
11

Thanks for this answer! I was looking for exactly the second "where" clause. That did the trick for me, awesome! – IvorG Feb 06 '22 at 08:46

score 11 · Answer 2 · edited Sep 11 '20 at 12:14

11

Why not use this approach. Works for me with zero errors.

let roles = ['ADMIN', 'STUDENT', SECRETARY];
   let user = await this.userRepository.createQueryBuilder('user')
         .andWhere('user.roles IN (:...roles)')
        .setParameter('roles', ...roles).count()

edited Sep 11 '20 at 12:14

Dharman

30,962
25
85
135

answered Sep 11 '20 at 12:09

uncle-tee

348
4
10

3

can you tell why spread operator(...) is used with roles in setParameter and IN clause ? – Pankaj Salunkhe Jun 10 '21 at 11:01

score 8 · Answer 3 · answered Apr 16 '20 at 20:31

8

You can use PostgreSQL array functions

let user = await this.userRepository
  .createQueryBuilder('user')
  .where('user.permissions @> ARRAY[:permissions]', { permissions: UserPermission.SUPERADMIN})
  .getOne();

Also, if you need to look for the presence of multiple array elements, you can use a spread operator (notice the ...):

.where('user.permissions @> ARRAY[:...permissions]', { permissions: ['foo', 'bar']})

answered Apr 16 '20 at 20:31

Mykhailo Zhuk

779
6
11

7

i am getting 'operator does not exist: text @> text[]' error – Kallol Medhi Sep 11 '20 at 10:26

score 6 · Answer 4 · answered Sep 28 '22 at 20:31

6

it is also possible to search array values in your DB cell using advanced find options:

const found = await repo.findOne({
    where: {
      roles: ArrayContains([role]),
    },
});

answered Sep 28 '22 at 20:31

Dmitry

71
1
3

score 0 · Answer 5 · answered Jun 02 '22 at 01:53

Try this helper functions that implements the exact syntax that you expected

import { FindOperator, Raw } from 'typeorm';

export const Includes = <T extends string | number>(value: T): FindOperator<T> =>
    Raw((columnAlias) => `:value = ANY(${columnAlias})`, { value });

How do I query an array in TypeORM

5 Answers5

Linked