TypeORM - JSONB where object in array LIKE value

Viewed 2006

I'm trying to port this query to TypeORM, and I cannot figure it out. Does anyone have any suggestions? I keep receiving column not found errors

SELECT id FROM study, jsonb_array_elements(findings) as elem WHERE elem->>'id'='patientName' AND  elem->>'value' ILIKE '%Dom%';

Here is my TypeORM query:

createQueryBuilder()
      .select(['id'])
      .addSelect('jsonb_array_elements(findings)', 'elem')
      .from(DbStudy, 'study')
      .andWhere(`"elem"->>"id" = 'patientName'`)
      .andWhere(`"elem"->>"value" ILIKE :patientName`, {
        patientName: `%${name}%`,
      });

It ends up generating a query like:

SELECT id, jsonb_array_elements(findings) AS "elem" FROM "study" "study" WHERE "elem"->>"id" = 'patientName' AND "elem"->>"value" ILIKE $1

Which doesn't work column "elem" does not exist.

Any suggestions?

0 Answers
Related