Invalid Raw() query in postgresql in jsonb fields

[warang580]

Issue description

Raw() query on a jsonb field in Postgresql doesn't escape correctly columnAlias with quotes

Expected Behavior

I'm trying to query entities based on jsonb properties.

@Entity()
export class PostAuthor {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @Column('jsonb')
  meta: any;
}

In database I have something like

author1.meta.traits = ['cool',  'fun']
author2.meta.traits = ['smart', 'nice']

And I'm trying to get only authors with a specific trait.
(This is a simple example that could be done differently but I need to make it work in jsonb because I need more complex needs later on with jsonb)

When I try to query using Raw(), jsonb field meta has incorrect generated SQL.

const authors = await this.postAuthorsRepository.find({
      where: {
        // It correctly escapes columnAlias with quotes on a non-jsonb field
        name: Raw((columnAlias) => `${columnAlias} LIKE :authorName`, {
          authorName: 'Jane%',
        }),

        // It doesn't espace columnAlias correctly on a jsonb field
        meta: Raw((columnAlias) => {
            return `${columnAlias}->'traits' ? :trait`;
            // gives
            return `PostAuthor.meta->'traits' ? :trait`;
            // instead of
            return `"PostAuthor"."meta"->'traits' ? :trait`;
            // or at least
            return `"PostAuthor".meta->'traits' ? :trait`;
          },
          { trait: 'fun' },
        ),
      },
    });

Actual Behavior

This is the generated query :

query: `SELECT "PostAuthor"."id" AS "PostAuthor_id", "PostAuthor"."name" AS "PostAuthor_name", "PostAuthor"."meta" AS "PostAuthor_meta", "PostAuthor"."created_at" AS "PostAuthor_created_at", "PostAuthor"."updated_at" AS "PostAuthor_updated_at" FROM "s01_post_author" "PostAuthor" WHERE (("PostAuthor"."name" LIKE $1) AND (PostAuthor.meta->'traits' ? $2))`,
  parameters: [
    'Jane%',
    'fun'
  ],

and the associated error :

driverError: error: missing FROM-clause entry for table "postauthor"

because it has generated (PostAuthor.meta->'traits' ? $2) instead of ("PostAuthor"."meta"->'traits' ? $2) like it did for name field in ("PostAuthor"."name" LIKE $1)

Steps to reproduce

@Entity()
export class SomeEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column('jsonb')
  meta: any;
}

and

entityRepository.find({
  where: {
    meta: Raw((columnAlias) => {
      return `${columnAlias}->'traits' ? :trait`;
    }, { trait: 'fun' }),
  },
})

My Environment

Dependency	Version
Operating System	Ubuntu 24.04.2 LTS
Node.js version	20.17.0
Typescript version	5.7.3
TypeORM version	0.3.25
NestJS version	11.0.1
NestJS TypeORM version	11.0.0

Additional Context

No response

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, but I don't know how to start. I would need guidance.

[BenjD90]

I have the same bug (with another table name, also with case-sensitive problem).

TypeORM version : 0.3.28
SGBD : Postgres version 17 (I don't think the version really matter for that bug)

I'll try to make a showcase to reproduce it.

[BenjD90]

Here is a sandbox that reproduce the bug :
https://codesandbox.io/p/devbox/upbeat-scooby-cfzq9j

$ ts-node ./src/main.ts
query: SELECT version()
query: SELECT * FROM current_database()
query: SELECT * FROM current_schema()
query: START TRANSACTION
query: SELECT * FROM current_schema()
query: SELECT * FROM current_database()
query: SELECT "table_schema", "table_name", obj_description(('"' || "table_schema" || '"."' || "table_name" || '"')::regclass, 'pg_class') AS table_comment FROM "information_schema"."tables" WHERE ("table_schema" = 'public' AND "table_name" = 'user')
query: SELECT * FROM "information_schema"."tables" WHERE "table_schema" = 'public' AND "table_name" = 'typeorm_metadata'
query: CREATE TABLE "user" ("id" SERIAL NOT NULL, "name" text NOT NULL, "details" json NOT NULL, CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))
query: COMMIT
query: START TRANSACTION
query: INSERT INTO "user"("name", "details") VALUES ($1, $2) RETURNING "id" -- PARAMETERS: ["joe","{\"aDetail\":\"something\"}"]
query: COMMIT
----- Init end ------
query: SELECT "User"."id" AS "User_id", "User"."name" AS "User_name", "User"."details" AS "User_details" FROM "user" "User" WHERE ((User.details->>'aDetail' = 'something'))
query failed: SELECT "User"."id" AS "User_id", "User"."name" AS "User_name", "User"."details" AS "User_details" FROM "user" "User" WHERE ((User.details->>'aDetail' = 'something'))
error: Error: 💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.

[Cprakhar]

You can write the operator with whitespace ${columnAlias} -> 'traits' and it works

Example

const loadedComments = await connection
    .getRepository(Comment)
    .findBy({
        metadata: Raw(
            (cplumnAlias) =>
                `${columnAlias} -> 'traits' ? :trait`,
            {
                trait: "cool",
            },
        ),
})

[BenjD90]

@Cprakhar That's where the bug is, this sandbox https://codesandbox.io/p/devbox/upbeat-scooby-cfzq9j show that this usage doesn't work 😕

Extract :

 const result = await userRepo.find({
      where: {
        details: Raw((alias) => `${alias}->>'aDetail' = 'something'`),
      },
    });

produce this wrong SQL query :

SELECT "user"."id"      AS "User_id",
       "user"."name"    AS "User_name",
       "user"."details" AS "User_details"
FROM   "user" "User"
WHERE  (( User.details ->> 'aDetail' = 'something' )); 
--        ↑←←←←← The error is here

[BenjD90]

@Cprakhar Thank you for the tests, I found that this fail :

        details: Raw((alias) => `${alias}->>'aDetail' = 'something'`),

but this work

        details: Raw((alias) => `${alias} ->>'aDetail' = 'something'`),

Very subtle difference ! This is the best workaround for now 😀 thanks !