如何用Sequelize ORM对PostgreSQL的JSONB数组对象执行条件查询

你的问题出在：metaData是JSONB数组类型，直接给它添加duration条件时，Sequelize会尝试访问数组本身的duration属性，而非数组中某个对象的duration，这和你的数据结构不匹配，导致查询失效。以下是两种可行的解决方案：

核心思路 #

需要检查JSONB数组中是否存在至少一个对象同时满足所有条件，可以通过PostgreSQL的JSONB函数结合Sequelize实现。

示例1：查询agentName='Ext 204'且duration >= 136的数据行 #

方案1：使用jsonb_path_exists（PostgreSQL 12+支持）

利用JSON路径表达式直接匹配数组元素的多条件：

const Op = Sequelize.Op;
const resp = await callModel.findAll({
  attributes: ['id', 'direction'],
  where: {
    [Op.and]: [
      Sequelize.where(
        Sequelize.literal(`jsonb_path_exists("metaData", '$[*] ? (@.agentName == "Ext 204" && @.duration >= 136)')`),
        true
      )
    ]
  }
});

生成的SQL：

SELECT "id", "direction" FROM "calls" AS "calls" 
WHERE jsonb_path_exists("metaData", '$[*] ? (@.agentName == "Ext 204" && @.duration >= 136)') = true;

方案2：使用EXISTS子查询（兼容低版本PostgreSQL）

通过jsonb_array_elements展开数组，逐个检查元素：

const resp = await callModel.findAll({
  attributes: ['id', 'direction'],
  where: {
    [Op.and]: [
      Sequelize.literal(`EXISTS (
        SELECT 1 FROM jsonb_array_elements("metaData") AS elem
        WHERE elem->>'agentName' = 'Ext 204'
          AND (elem->>'duration')::int >= 136
      )`)
    ]
  }
});

示例2：查询agentName='Ext 204'且startedAt >= '2020-08-31 10:07:00'的数据行 #

方案1：JSON路径表达式

const resp = await callModel.findAll({
  attributes: ['id', 'direction'],
  where: {
    [Op.and]: [
      Sequelize.where(
        Sequelize.literal(`jsonb_path_exists("metaData", '$[*] ? (@.agentName == "Ext 204" && @.startedAt >= "2020-08-31 10:07:00")')`),
        true
      )
    ]
  }
});

方案2：EXISTS子查询

const resp = await callModel.findAll({
  attributes: ['id', 'direction'],
  where: {
    [Op.and]: [
      Sequelize.literal(`EXISTS (
        SELECT 1 FROM jsonb_array_elements("metaData") AS elem
        WHERE elem->>'agentName' = 'Ext 204'
          AND elem->>'startedAt' >= '2020-08-31 10:07:00'
      )`)
    ]
  }
});

安全优化：参数化查询 #

为避免SQL注入，建议用参数替换硬编码值：

const agentName = 'Ext 204';
const minDuration = 136;
const resp = await callModel.findAll({
  attributes: ['id', 'direction'],
  where: Sequelize.literal(`EXISTS (
    SELECT 1 FROM jsonb_array_elements("metaData") AS elem
    WHERE elem->>'agentName' = :agentName
      AND (elem->>'duration')::int >= :minDuration
  )`),
  replacements: { agentName, minDuration }
});

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