Sequelize如何基于JSONB字段内的键实现include关联查询

Sequelize 原生没有提供直接在关联配置的key/foreignKey参数中填写JSON路径的能力，但由于include本质是生成SQL的JOIN逻辑，只要自定义JOIN的ON匹配条件，将JSONB字段内的属性提取出来和关联表主键做匹配，就可以实现需求。

具体实现步骤（以PostgreSQL为例，其他数据库可对应调整JSON函数） #

1. 基础模型定义 #

先定义两个基础模型，注意关闭默认外键的自动创建：

const { Sequelize, DataTypes, Op } = require('sequelize');
const sequelize = new Sequelize(/* 数据库连接配置 */);

const Store = sequelize.define('Store', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  name: DataTypes.STRING
});

const Item = sequelize.define('Item', {
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
  someJsonbField: DataTypes.JSONB
});

2. 配置自定义关联 #

定义关联时通过on参数自定义匹配规则，禁用默认外键约束：

// Store 关联多个 Item
Store.hasMany(Item, {
  // 传一个不存在的字段名，跳过默认外键字段的自动创建
  foreignKey: '__unused_fk',
  // 关闭数据库层面的外键约束生成
  constraints: false,
  // 自定义JOIN的ON匹配条件
  on: sequelize.where(
    sequelize.col('Store.id'),
    Op.eq,
    // 用PG的JSON操作符提取JSONB内的storeId，转成整数类型匹配
    sequelize.literal(`(Item.someJsonbField ->> 'storeId')::INTEGER`)
  )
});

// 反向关联：Item 归属一个 Store
Item.belongsTo(Store, {
  foreignKey: '__unused_fk',
  constraints: false,
  on: sequelize.where(
    sequelize.col('Store.id'),
    Op.eq,
    sequelize.literal(`(Item.someJsonbField ->> 'storeId')::INTEGER`)
  )
});

3. 正常使用include查询 #

关联配置完成后，就可以和普通关联一样用include查询：

// 查询id为2的Store下所有Item
const storeRes = await Store.findByPk(2, { include: Item });

// 查询Item时带出关联的Store
const itemRes = await Item.findAll({ include: Store });

如果只是单次查询需要这种关联，不想全局定义关联规则，也可以直接在include里写匹配条件：

const items = await Item.findAll({
  include: {
    model: Store,
    required: true, // 等价于INNER JOIN
    where: sequelize.where(
      sequelize.col('Store.id'),
      Op.eq,
      sequelize.literal(`(Item.someJsonbField ->> 'storeId')::INTEGER`)
    )
  }
});

注意事项 #

• 这种方式无法使用数据库原生外键约束，JSONB内存储的关联id合法性、数据一致性需要业务代码自行保证，存错类型、缺字段、存不存在的id都会导致关联匹配失败。
• 性能层面：如果数据量较大，一定要给JSONB内的关联键建表达式索引，否则会触发全表扫描，查询效率极低。PG环境建索引参考：

  CREATE INDEX idx_item_storeid_from_jsonb ON items (((someJsonbField ->> 'storeId')::INTEGER));
  

• JSON取值语法需要和使用的数据库匹配：PostgreSQL用->>/->操作符，MySQL用JSON_EXTRACT(字段, '$.键路径')，SQLite用json_extract(字段, '$.键路径')，替换literal里的SQL片段即可。
• 常规业务场景优先把关联键定义为模型直属字段，不管是性能、可维护性还是数据一致性都更优，本方案仅适用于无法修改表结构、必须将关联键存在JSONB内的特殊场景。

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