如何使用Sequelize operators实现Postgres jsonb字段条件计数查询

核心逻辑 #

基于PostgreSQL的jsonb内置函数统计instances数组中type为bbox的元素数量，再通过Sequelize封装实现过滤条件。

实现代码 #

方案1：兼容所有PostgreSQL版本（推荐） #

通过子查询展开数组统计符合条件的元素数，Sequelize实现如下：

// 替换YourModel为你实际的Sequelize模型名，sequelize为你初始化的Sequelize实例
const qualifiedCount = await YourModel.count({
  where: sequelize.where(
    sequelize.literal(`(SELECT COUNT(*) FROM jsonb_array_elements(instances) elem WHERE elem->>'type' = 'bbox')`),
    '>',
    2
  )
});

对应底层原生SQL逻辑参考：

SELECT COUNT(*) FROM your_table_name
WHERE (
  SELECT COUNT(*) FROM jsonb_array_elements(your_table_name.instances) AS elem
  WHERE elem->>'type' = 'bbox'
) > 2;

方案2：PostgreSQL 12+ 简化写法 #

利用高版本PostgreSQL支持的jsonpath语法简化查询：

const qualifiedCount = await YourModel.count({
  where: sequelize.where(
    sequelize.fn(
      'jsonb_array_length',
      sequelize.fn(
        'jsonb_path_query_array',
        sequelize.col('instances'),
        sequelize.literal(`'$[*] ? (@.type == "bbox")'`)
      )
    ),
    '>',
    2
  )
});

注意事项 #

• 数据量较大时可对instances字段创建GIN索引优化查询性能，建索引语句：CREATE INDEX idx_instances_type ON your_table_name USING GIN (instances jsonb_path_ops);
• 代码返回的qualifiedCount即为符合条件的总行数

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