如何用Knex实现PostgreSQL的NOT EXISTS+jsonb_array_elements查询

对应原生PostgreSQL逻辑 #

先明确需求对应的原生SQL（和你可运行的语句逻辑一致）：

SELECT *
FROM your_table
WHERE NOT EXISTS (
  SELECT 1
  FROM jsonb_array_elements(column1) AS elem
  WHERE elem ? 'answer'
)
AND column2 IS NOT NULL;

Knex实现代码（推荐写法） #

knex('your_table')
  .whereNotExists(function() {
    // 子查询展开jsonb数组，检查是否存在含'answer'字段的元素
    this.select(knex.raw('1'))
      .from(knex.raw('jsonb_array_elements(??) AS elem', ['column1']))
      .whereRaw('elem ? ?', ['answer']);
  })
  .whereNotNull('column2');

代码说明 #

• whereNotExists 对应原生SQL的NOT EXISTS，用于排除存在符合条件元素的行
• jsonb_array_elements(??) 使用Knex的占位符??安全引用列名，避免SQL注入，同时展开jsonb数组为行
• elem ? ? 是PostgreSQL的jsonb操作符，检查当前数组元素是否包含answer字段，第二个?是参数占位符，传入字段名
• whereNotNull('column2') 直接实现column2 IS NOT NULL的筛选条件

简洁替代写法（使用jsonb_path_exists） #

如果你的PostgreSQL版本支持jsonb_path_exists（PostgreSQL 12+），可以用更简洁的路径查询：

knex('your_table')
  .whereRaw('NOT jsonb_path_exists(??, \'$[*] ? (@.answer)\')', ['column1'])
  .whereNotNull('column2');

路径表达式$[*] ? (@.answer)的含义是：检查数组中是否存在任意元素包含answer字段，NOT取反后即为不存在该元素的行。

内容的提问来源于stack exchange，提问作者Adi Nugraha