Search item in array at postgres using typeorm

Question

Database: postgres

ORM: Typeorm

Framework: express.js

I have a Table in which one of the fields, named projects is an array of strings. The type is set to "varchar" in the migration and the de decorator is set to "simple-array".

In my get route if I receive a query ?project=name_of_the_project it should try to find the project in the simple-array.

For the search my get route is like this:

studentsRouter.get("/", async (request, response) => {
    const { project } = request.query;
    const studentRepository = getCustomRepository(StudentRepository);
    const students = project
        ? await studentRepository
                .createQueryBuilder("students")
                .where(":project = ANY (students.projects)", { project: project })
                .getMany()
        : await studentRepository.find();
    // const students = await studentRepository.find();
    return response.json(students);
}); 

The problem is that I´m getting an error saying that the right side should be an array.

(node:38971) UnhandledPromiseRejectionWarning: QueryFailedError: op ANY/ALL (array) requires array on right side
    at new QueryFailedError (/Users/Wblech/Desktop/42_vaga/src/error/QueryFailedError.ts:9:9)
    at Query.callback (/Users/Wblech/Desktop/42_vaga/src/driver/postgres/PostgresQueryRunner.ts:178:30)
    at Query.handleError (/Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/query.js:146:19)
    at Connection.connectedErrorMessageHandler (/Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/client.js:233:17)
    at Connection.emit (events.js:200:13)
    at /Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/connection.js:109:10
    at Parser.parse (/Users/Wblech/Desktop/42_vaga/node_modules/pg-protocol/src/parser.ts:102:9)
    at Socket.<anonymous> (/Users/Wblech/Desktop/42_vaga/node_modules/pg-protocol/src/index.ts:7:48)
    at Socket.emit (events.js:200:13)
    at addChunk (_stream_readable.js:294:12)
(node:38971) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:38971) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.

It has to be an array of string in this field and I can´t use an foreignKey.

Please find bellow my migration and model related to this issue:

Migration:

import { MigrationInterface, QueryRunner, Table } from "typeorm";

export class CreateStudents1594744103410 implements MigrationInterface {
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.createTable(
            new Table({
                name: "students",
                columns: [
                    {
                        name: "id",
                        type: "uuid",
                        isPrimary: true,
                        generationStrategy: "uuid",
                        default: "uuid_generate_v4()",
                    },
                    {
                        name: "name",
                        type: "varchar",
                    },
                    {
                        name: "intra_id",
                        type: "varchar",
                        isUnique: true,
                    },
                    {
                        name: "projects",
                        type: "varchar",
                        isNullable: true,
                    },
                ],
            })
        );
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.dropTable("students");
    }
}

Model:

import { Entity, Column, PrimaryGeneratedColumn } from "typeorm";

@Entity("students")
class Student {
    @PrimaryGeneratedColumn("uuid")
    id: string;

    @Column()
    name: string;

    @Column()
    intra_id: string;

    @Column("simple-array")
    projects: string[];
}

export default Student;

EDIT - 01

In the docs I found out that the simple-array stores the strings separated by a comma. I thing this means it is a string with words separated by a comma. In this case is there a way to find the which row has the string in the projects field?

Link - https://gitee.com/mirrors/TypeORM/blob/master/docs/entities.md#column-types-for-postgres

Edit 02

The field projects stores the projects that the students are doing, so the database returns this json:

  {
    "id": "e586d1d8-ec03-4d29-a823-375068de23aa",
    "name": "First Lastname",
    "intra_id": "flastname",
    "projects": [
      "42cursus_libft",
      "42cursus_get-next-line",
      "42cursus_ft-printf"
    ]
  },

Answer 1

Based on the comments and updates to the question, @WincentyBertoniLech determined that the ORM was storing the projects array as a comma-delimited text value in the students.projects column.

We can use string_to_array() to turn this into the proper where criterion:

.where(":project = ANY ( string_to_array(students.projects, ','))", { project: project })