javascript - Knexjs PgSQL json 查询

Question

我在 Postgres 中有一列存储一些 JSON 数据。 JSON 没有定义的架构，但应该可以搜索具有某个指定键的所有记录。

我正在使用 KnexJS 构建查询，到目前为止我想到了这个:

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

但它不起作用，因为我认为无法指定类型。

仍然，当我尝试像这样手动指定它时:

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

还是不行!这是使用 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

关于如何实现此目标的任何想法？

提前致谢!

Answer 1

要从 JSONB 字段中搜索特定键，您可以使用 ?、?| 和 ?& 运算符，但从问题我相信你是实际上试图找到特定键具有特定值的所有行。

PostgreSQL 协议(protocol)不支持将类型作为绑定(bind)传递，因此您需要像第二个示例中那样将其作为原始字符串传递。

但是你仍然在那里做一些非常奇怪的事情:

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

data->>? 以字符串形式返回您的 json 属性值。然后将其转换为 bool 值并将其与某个值 JSON.parse(value) 进行比较，该值可能几乎是任何值。

从错误 { key: 'admin', value: 'true', type: 'boolean' } 看来你的值实际上已经是一个字符串，所以这应该有效:

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

无论如何，由于对“真实”字符串进行了显式转换，您的第二个示例也应该有效。我添加了 knex 示例，表明您的案例应该有效:

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 查询的更多信息，请参见此处 https://www.vincit.fi/en/blog/objection-js-postgresql-power-json-queries/基于 knex 的 ORM objection.js 也明确支持 postgres jsonb 查询。