测试jsonb数组以查看它是否包含一个或多个元素？

Question

我有一个包含data jsonb列的user表。在这种情况下，我有一个cars数组，其中可以包含0-N辆汽车。

我尝试执行类似这样的查询，以查找用户的cars数组是否包含丰田或三菱，但抛出了一个错误

await knex('users.record')
    .where('created_at', '>', '2019-05-21')
    .select('data')
    .whereRaw('cars', ['Toyota', 'Mitsubishi']) // this seems wrong
    .returning('*');

['Toyota', 'Mitsubishi', 'Honda', 'Ford']是用户的cars数组的一个示例

Answer 1

尝试替换

.whereRaw('cars', ['Toyota', 'Mitsubishi'] 

使用

where('cars', 'like','%Toyota%').orWhere('cars', ,like, '%Mitsubishi%')

Answer 2

您可以使用jsonb contains operator

SELECT * FROM users
WHERE  created_at > '2019-05-21'
AND   (data @> '{"cars":["Toyota"]}' OR
       data @> '{"cars":["Mitsubishi"]}');

纯SQL，我对knex不熟悉。

这可以通过索引来支持。请参见：

• What's the proper index for querying structures in arrays in Postgres jsonb?

Answer 3

我认为它在Knex中会看起来像这样(有一个小的helper函数来减少重复)：

const hasCar = brand => knex.raw(
  `?? @> ?::jsonb`,
  [
    'data',
    JSON.stringify({ cars: [ brand ]})
  ]
)

knex('users.record')
  .where(hasCar('Mitsubishi'))
  .orWhere(hasCar('Toyota'))