TypeORM: Find Rows Where Column Value is IN an Array

This short and straight-to-the-point article shows you 2 different ways to use the WHERE IN query in TypeORM (in case you want to search for rows whose column values are an element of a given array). The first approach is to use the findBy() method, while the second one is to use a query builder.

In the upcoming examples, we’ll work with an entity called Product:

// Product entity
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
} from 'typeorm';

@Entity({name: 'products'})
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column()
  color: string;

  @Column({default: 0})
  price: number
}

Using the findBy() method

Let’s say we want to retrieve products with colors in the following colors: blue, red, orange, and green. We will do the following:

1. Import the In operator:

import { In } from 'typeorm';

2. Call the findBy() method like so:

const productRepository = dataSource.getRepository(Product);

// find products
const products = await productRepository.findBy({ 
    color: In(['blue', 'red', 'orange', 'green']) 
})

This will execute this query:

SELECT * FROM "products" WHERE "color" IN ('blue','red','orange','green')

Using query builder

It also does find products with color in a certain list, but this time we use a QueryBuilder:

const productRepository = dataSource.getRepository(Product);

const products = await productRepository
      .createQueryBuilder('product')
      .where('product.color IN (:...colors)', {
        colors: ['blue', 'red', 'orange', 'green'],
      })
      .getMany();

console.log(products);

This code will perform the same query as the preceding example:

SELECT * FROM "products" WHERE "color" IN ('blue','red','orange','green')

That’s it. Further reading:

• How to Store JSON Object with TypeORM
• TypeORM: Add Columns with Array Data Type
• PostgreSQL: How to Rename a Column of a Table
• Using ENUM Type in TypeORM
• Pagination in TypeORM (Find Options & QueryBuilder)

You can also check out our database topic page for the latest tutorials and examples.