typeORM으로 mysql 사용 시, JSON 타입 컬럼의 내부 데이터 활용하기

목적

엔티티를 구성할 때, 부가적인 정보들은 metadata라는 컬럼명으로 json 타입으로 구성하는 경우가 있습니다. 만약, typeORM을 사용하며 json 타입 내부 객체의 키벨류 값으로 조건문을 걸거나 값을 가져오는 등의 로직 구현에서 어려움을 겪었다면 이번 글이 관련 문제를 해결하는 데 도움이 되었으면 좋겠습니다.

 

이번 글에서는 mysql & typeORM & TypeScript 를 사용할 때, json 타입의 컬럼 내부의 정보를 활용하는 방법을 살펴볼 것입니다.

---

목차

- Entity 구성
- 1) typeORM find 옵션으로 이름 검색하기
- 2) typeORM find 옵션으로 json 내부 데이터 검색하기
- 3) typeORM 쿼리빌더로 json 내부 데이터를 활용하여 SUM 구하기

Entity 구성

유저의 이름, 이메일, 포인트, 나이와 지역을 포함하는 메타데이터(json)로 구성했습니다.

@Entity({ name: "user" })
export class UserTest {
    @PrimaryGeneratedColumn()
    id: number;

    @Column({ type: "varchar", length: 20 })
    name: string;

    @Column({ type: "varchar" })
    @IsEmail()
    email: string;

    @Column({ type: "bigint", nullable: true })
    point?: number;

    @Column({ type: "json" })
    metadata: UserMetadata = {};

    @CreateDateColumn()
    createdAt: Date;

    @UpdateDateColumn()
    updatedAt: Date;

    @DeleteDateColumn()
    deletedAt?: Date;
}

interface UserMetadata {
    age?: number;
    location?: string;
}

 

 

1) typeORM find 옵션으로 이름 검색하기

• 삼항연산자를 사용하지 않는 경우, 빈배열이 나올 것입니다. 왜냐하면 다음과 같이 where 조건을 배열로 구성하여 하나의 키워드로 다수의 컬럼을 검색하고자 하는 경우(ex. 하나의 키워드로 유저의 이름과 이메일을 검색하는 경우)에는 단건 조건처럼 undefined 로 적용이 되지 않기 때문에(typeORM이 자동 적용으로 적용해줌) 삼항연산자로 명시를 해주어야 합니다.

    async getUsersByName(name?: string, limit?: number, start?: number): Promise<UserTest[]> {
        return this.datasource.getRepository(UserTest).find({
            where: location ? [{ name: Like(`%${name}%`) }] : undefined,
            select: { id: true, name: true, email: true },
            order: { id: "DESC" },
            take: limit,
            skip: start,
        });
    }

2) typeORM find 옵션으로 json 내부 데이터 검색하기

• json 내부의 location을 검색하고자 하는 경우 다음과 같이 typeORM이 제공하는 Raw() 를 활용합니다.
• metadata는 컬럼명이고, location은 내부의 키값입니다.

    async getUsersByLocation(
        location?: string,
        limit?: number,
        start?: number
    ): Promise<UserTest[]> {
        return this.datasource.getRepository(UserTest).find({
            where: {
                metadata: Raw(() => `metadata -> '$.location' = :location`, { location: location }),
            },
            order: { id: "DESC" },
            take: limit,
            skip: start,
        });
    }

3) typeORM 쿼리빌더로 json 내부 데이터를 활용하여 SUM 구하기

• 쿼리빌더 사용 시, 쿼리에 사용할 별칭을 설정해주어야 합니다.
• json 내부 데이터를 활용하는 방법은 2번에서 설명한 방법과 유사하나, Raw() 대신 JSON_EXTRACT를 활용한다는 점이 다릅니다.
• 쿼리문 마지막에 getRawOne() 을 해야 결과값 객체를 얻을 수 있습니다.

    async getUsersTotalPointByAge(age: number): Promise<{ age: number; point: number }> {
        const usersTotalPoint = await this.datasource
            .getRepository(UserTest)
            .createQueryBuilder("user")
            .select("SUM(user.point)", "point")
            .where("JSON_EXTRACT(user.metadata, '$.age') = :age", {
                age: age,
            })
            .getRawOne();

        return Object.assign({ age: age }, usersTotalPoint);
    }