Knexjs PgSQL json查询

我有一个存储一些JSON数据的Postgres中的列。 JSON没有定义的模式,但应该可以search所有具有某个指定键的logging。

我使用KnexJS来build立查询,到目前为止我想出了这个:

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

但是这不起作用,因为我不认为有可能指定types。

不过,当我尝试像这样手动指定它时:

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

它仍然不起作用! 这是使用DEBUG:knex:*时来自控制台的日志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 

任何想法,我怎么能做到这一点?

提前致谢!

       

网上收集的解决方案 "Knexjs PgSQL json查询"

要从JSONB字段search特定的键,你可以使用??|?&运营商,但从问题我相信你实际上是试图find所有行中的某些关键具有一定的价值。

PostgreSQL协议不支持作为绑定的传递types,因此您需要像在第二个示例中那样将其作为原始string传递。

然而,你仍然在做一些非常奇怪的事情:

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

data->>? 以stringforms返回您的json属性值。 然后将其转换为布尔值,并将其与一些值相比较,即JSON.parse(值),这可能几乎是任何东西。

从错误{ key: 'admin', value: 'true', type: 'boolean' }看来你的价值实际上已经是一个string,所以这应该工作:

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

无论如何,也是你的第二个例子应该已经工作,因为你的“真正的”string的显式转换完成。 我添加了一个例子,显示你的情况应该工作:

 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 } } ] 

更多信息如何用postgresql做jsonb查询可以在这里findhttps://www.vincit.fi/en/blog/objection-js-postgresql-power-json-queries/也knex基于ORM objection.js有明确的支持postgres jsonb查询。

绑定如何工作:

首先,您需要知道如何在您使用的库上绑定工作。

从您提供的debugging日志中可以看出,您将值type为string:

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

因此,第一个查询及其实际的SQL翻译是:

 // 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; 

这提供了明确的语法错误,也可以在Postgres日志中看到。

第二个查询(当您手动投射)失败,因为您保留3个绑定参数,而只使用2和第二个参数是由数组顺序type而不是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' 

这自然会失败。

当你在这里,一些SQL技巧:

Postgres给你几个其他的选项/样式你可以如何执行你的查询,这里是一些其他的例子(都给出了相同的结果):

 // 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 

你喜欢什么风格完全取决于你的偏好,我只是指出你怎么能做到这一点:)

希望,帮助:)