Knexjs PgSQL json query

To search specific key from JSONB field you can use ?, ?| and ?& operators, but from question I believe you are actually trying to find all rows where certain key has certain value.

PostgreSQL protocol doesn't support passing type as a binding, so you need to pass it as a raw string as you've done in the second example.

However you are still doing something really strange there:

tx.select('*').from('table')
  .whereRaw('cast(data->>? as boolean) = ?', [key, JSON.parse(value)]));

data->>? returns your json attribute value as a string. Then you convert it to boolean and compare it to some value that is JSON.parse(value) which might be pretty much anything.

From the error { key: 'admin', value: 'true', type: 'boolean' } it seems that your value is actually already a string, so this should work:

tx.select('*').from('table')
  .whereRaw('data->>? = ?', [key, JSON.parse(value)]));

Anyways, also yours second example should have worked due to explicit cast done to your 'true' string. I added knex example showing that your case should work:

await knex.schema.createTable('test2', t => { 
  t.increments('id'); 
  t.jsonb('test');
});
await knex('test2').insert([
  { test: '{ "a": true, "b": false }' },
  { test: '{ "b": true, "a": false }' }
]);
await knex('test2').whereRaw('cast(test->>? as boolean) = ?', ['a', 'true']);

// outputs: [ anonymous { id: 1, test: { a: true, b: false } } ]

More information how to do jsonb queries with postgresql can be found here https://www.vincit.fi/en/blog/objection-js-postgresql-power-json-queries/ also knex based ORM objection.js has explicit support for postgres jsonb queries.

How bindings work:

First of all you need to know how binding work at the lib you use.

From the debug log you provided it shows that you value type as string:

key: 'admin', value: 'true', type: 'boolean'

Therefore the first query and its actual SQL translation is:

// Your code:
tx.select('*').from('table')
.whereRaw('cast(data->>? as ?) = ?', [key, type, JSON.parse(value)]));

// Actual SQL:
SELECT * FROM "table" WHERE cast(data->>'admin' as 'boolean') = true;

This provides clear syntax error that is visible in your Postgres logs also.

The second query (when you cast manually) fails because you kept 3 binding parameters while using only 2 and second parameter is by array order type instead of value:

// Your code:
tx.select('*').from('table')
.whereRaw('cast(data->>? as boolean) = ?', [key, JSON.parse(value)]));

// Actual SQL:
SELECT * FROM "table" WHERE cast(data->>'admin' as boolean) = 'undefined'

Which naturally fails.

While you are at it, some SQL tips:

Postgres gives you few other options/styles how you can execute your query and here are some other examples (all giving same results):

// Casting with `=` s
SELECT * FROM table WHERE (data ->> 'admin')::boolean = TRUE            // finds all `true` values
SELECT * FROM table WHERE NOT ( (data ->> 'admin')::boolean = FALSE )   // finds all 'false' values

// Since every condition in WHERE ends up boolean you can avoid
// using `=` comparsion and shorten the code
SELECT * FROM table WHERE (data ->> 'admin')::boolean                   // finds all `true` values
SELECT * FROM table WHERE NOT ( (data ->> 'admin')::boolean )           // finds all 'false' values

// When using JSONB data type you can use `->` operator instead
SELECT * FROM table WHERE data -> 'admin' = 'true'                      // finds all `true` values
SELECT * FROM table WHERE NOT ( data -> 'admin' = 'false' )             // finds all 'false' values

What style you prefer is totally up to your preference and I'm just pointing out how else you can do it :)

Hopefully, helps :)