How to search item in array at postgres using typeorm

When working with TypeORM to manage a PostgreSQL database, you may encounter scenarios where you need to search for specific items within array fields. I'll walk you through several methods for searching specific items in PostgreSQL arrays using TypeORM.

First, ensure that your entity defines an array field. For example, let's define a User entity with a string array field tags:

typescript
import { Entity, PrimaryGeneratedColumn, Column } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column("text", { array: true })
  tags: string[];
}

1. Using @Query for Direct Queries

Assume you need to find all users whose tags array contains the specific tag "nodejs". You can directly execute this query using SQL statements within TypeORM:

typescript
import { EntityRepository, Repository } from 'typeorm';
import { User } from './user.entity';

@EntityRepository(User)
export class UserRepository extends Repository<User> {
  findUsersByTag(tag: string): Promise<User[]> {
    return this.createQueryBuilder("user")
      .where(" :tag = ANY(user.tags)", { tag })
      .getMany();
  }
}

In this example, the ANY function in PostgreSQL checks whether the specified value exists within the array.

2. Using QueryBuilder

This approach offers greater flexibility as it allows chaining additional query conditions. Here's how to use QueryBuilder to find users with specific tags:

typescript
findUsersByTag(tag: string): Promise<User[]> {
  return this.createQueryBuilder("user")
    .where("user.tags @> ARRAY[:tag]", { tag })
    .getMany();
}

In this example, the @> operator in PostgreSQL checks if the left array contains the right array.

3. Using TypeORM's find Method

For simpler queries, you can leverage TypeORM's find method with ILIKE for array comparisons. This method is suitable when you need a full match on the array:

typescript
findUsersByWholeTags(tags: string[]): Promise<User[]> {
  return this.find({ where: { tags } });
}

This method assumes you require a complete match on the tags array, not just matching one item within it.

Conclusion

When working with PostgreSQL arrays, TypeORM provides multiple flexible methods to query arrays containing specific items. You can use direct SQL queries, leverage QueryBuilder for complex queries, or use the find method for straightforward searches. Each method has specific use cases, and you should choose the most appropriate one based on your requirements.

I hope these examples help you better understand how to use TypeORM in practical scenarios to manipulate array data in PostgreSQL.