How do I query an array in TypeORM

When using TypeORM to interact with databases, we often need to query data stored in array fields. In PostgreSQL, such fields are typically defined as array types. In TypeORM, we can query these array fields in several ways, and here I will outline common query approaches.

1. Checking if an Array Contains a Value

Consider an entity named User with a hobbies field of string array type. We aim to query users whose hobbies include 'reading'.

typescript
import { getRepository } from "typeorm";
import { User } from "./entity/User";

async function findUsersByHobby() {
    const userRepository = getRepository(User);
    const users = await userRepository.find({
        where: {
            hobbies: "reading"
        }
    });
    return users;
}

2. Using @>, <@, && Operators

PostgreSQL offers specialized array operators for more complex queries. For instance:

• @> checks if the left array contains the right array.
• <@ checks if the left array is contained by the right array.
• && checks if two arrays have any intersection.

For example, to query users whose hobbies include both 'reading' and 'cooking':

typescript
import { getRepository } from "typeorm";
import { User } from "./entity/User";

async function findUsersByMultipleHobbies() {
    const userRepository = getRepository(User);
    const users = await userRepository.createQueryBuilder("user")
        .where("user.hobbies @> ARRAY[:...hobbies]", { hobbies: ["reading", "cooking"] })
        .getMany();
    return users;
}

3. Querying Array Length

Sometimes we need to query based on array length, such as finding users with hobbies exceeding 3 items.

typescript
import { getRepository } from "typeorm";
import { User } from "./entity/User";

async function findUsersByHobbiesCount() {
    const userRepository = getRepository(User);
    const users = await userRepository.createQueryBuilder("user")
        .where("array_length(user.hobbies, 1) > :count", { count: 3 })
        .getMany();
    return users;
}

The above methods demonstrate how to query arrays in TypeORM. By leveraging native SQL features and TypeORM's capabilities, we can effectively handle various array-related query needs.