如何用Knex构建含Postgres JSONB "?"操作符的Inner Join查询？

目标SQL #

select "data"."parent"."name"
from "data"."parent"
         inner join "data"."child" on child_hashes::jsonb ? "data"."child".hash
where "data"."child"."name" = 'Hans'

注：child_hashes是存储子哈希有序数组的JSON类型列

失败尝试1：常规查询写法 #

knex
    .table(this.parentTableName)
    .innerJoin(this.childTableName, "child_hashes::jsonb", "?", `${this.childTableName}.hash`)
    .where(`${this.childTableName}.name`, name)
    .pluck(`${this.parentTableName}.name`);

生成的错误SQL：

select "data"."parent"."name"
from "data"."parent"
         inner join "data"."child" on "child_hashes::jsonb" ? "data"."child".hash
where "data"."child"."name" = 'Hans'

问题：Knex自动给child_hashes::jsonb加上双引号，导致PostgreSQL无法识别这是JSONB类型转换操作。

失败尝试2：回调内使用原生查询 #

knex
    .table(this.parentTableName)
    .innerJoin(this.childTableName, (query) => {
        query.on(this.knex.raw("child_hashes::jsonb ? ?.hash", ["?", this.childTableName]));
    })
    .where(`${this.childTableName}.name`, name)
    .pluck(`${this.parentTableName}.name`);

生成的错误SQL：

select "data"."parent"."name"
from "data"."parent"
         inner join "data"."child" on child_hashes::jsonb '?' "data"."child".hash
where "data"."child"."name" = 'Hans'

问题：Knex把?操作符误判为参数占位符，自动添加单引号；同时参数绑定逻辑混乱，会错误复用where子句的name值。

正确解法 #

直接在innerJoin的条件中使用完整的原生SQL片段，用Knex的占位符语法正确处理表名，避免自动引号干扰：

knex
    .table(this.parentTableName)
    .innerJoin(
        this.childTableName,
        knex.raw('??.child_hashes::jsonb ? ??.hash', [
            this.parentTableName,
            this.childTableName
        ])
    )
    .where(`${this.childTableName}.name`, name)
    .pluck(`${this.parentTableName}.name`);

核心要点 #

• 用knex.raw()编写连接条件，绕过Knex对特殊语法的错误处理
• ??是Knex的表/字段占位符，会自动生成带正确双引号的引用（比如"data"."parent".child_hashes::jsonb）
• ?操作符直接写在原生SQL中，不会被识别为参数占位符
• 通过数组传递表名参数，确保表名被正确解析

生成的SQL与目标完全一致：

select "data"."parent"."name"
from "data"."parent"
         inner join "data"."child" on "data"."parent".child_hashes::jsonb ? "data"."child".hash
where "data"."child"."name" = 'Hans'

内容的提问来源于stack exchange，提问作者Meyra