Building knex innerJoin with Postgres JSONB "?" operator?

Question

I want to create a query like this with knex:

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 is a JSON column that stores an ordered array with child hashes.

1. Normal query

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

this returns the "child_hashes::jsonb" in double quotes

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

2. innerJoin with raw query in callback

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`);

this returns the ? operator in single quotes

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

Edit: The second attempt also didn't work without the binding of this.childTableName. It uses "name" from the where clause below.

Meyra · Accepted Answer · 2024-05-07 05:48:11Z

1

It worked after escaping the ? in the second query:

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`);

edited May 7, 2024 at 5:48

answered May 6, 2024 at 15:56

Meyra

596 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Building knex innerJoin with Postgres JSONB "?" operator?

1. Normal query

2. innerJoin with raw query in callback

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1. Normal query

2. innerJoin with raw query in callback

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related