Knexjs PgSQL json query

Question

I have a column in a Postgres that stores some JSON data. The JSON has no defined schema, but it should be possible to search all records that have some specified key.

I'm using KnexJS to build the queries and so far I came up with this:

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

But it's not working because I don't think it will be possible to specify the type.

Still, when I try to specify it manually like this:

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

It still doesn't work! This is the log from the console when using DEBUG:knex:*

{ key: 'admin', value: 'true', type: 'boolean' }
  knex:tx trx1: Starting top level transaction +0ms
  knex:pool INFO pool postgresql:pg:client0 - dispense() clients=1 available=0 +2ms
  knex:client acquired connection from pool: __knexUid2 +38ms
  knex:query BEGIN; +2ms
  knex:bindings undefined +1ms
  knex:query select * from "contexts" where cast(data->>? as boolean) = ? +18ms
  knex:bindings [ 'admin', true ] +0ms
  knex:query COMMIT; +9ms
  knex:bindings undefined +0ms
  knex:tx trx1: releasing connection +6ms
  knex:client releasing connection to pool: __knexUid2 +1ms
  knex:pool INFO pool postgresql:pg:client0 - dispense() clients=0 available=1 +1ms

Any ideas on how I can accomplish this?

thanks in advance!

Solution 1

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.

Solution 2

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 :)