数组的属性删除资源?"/>
如何基于Sequelize(postgres)中JSONB数组的属性删除资源?
我有这样的模型:
module.exports = (sequelize, DataTypes) => {
const MatterResource = sequelize.define('MatterResource', {
...
attached_resources: {
type:DataTypes.ARRAY(DataTypes.JSONB)<<<<,
allownull: true,
defaultValue: []
}
}, {});
现在,我正在尝试删除whereId =?和public_id =?在JSONB数组中,但是它不起作用,关于我应该做什么的任何想法对吗?
这是我目前正在做的
try {
const resource = await model.MatterResource.destroy({
where: {
matterId,
attached_resources:{
[sequelize.Op.contains]: [ { public_id : req.params.public_id } ]<<<<< issue
}
}
})
console.log('RESOURCE', resource);
return res.status(204).json({
status: 204,
resource,
message: 'Resource deleted'
});
} catch(error){
return res.status(500).json({
err: error.message
})
}
这是postgres中生成的SQL语句:
Executing (default): DELETE FROM "MatterResources" WHERE "matterId" = 1 AND "attached_resources" @> ARRAY['{"public_id":"cl9lvbwv0ern0evjhwj7"}']::JSONB[]
它应该按预期工作:
import { sequelize } from '../../db';
import { DataTypes, Model, Op } from 'sequelize';
class MatterResource extends Model {}
MatterResource.init(
{
attached_resources: {
type: DataTypes.ARRAY(DataTypes.JSONB),
allowNull: true,
defaultValue: [],
},
},
{ sequelize, modelName: 'matter_resource' },
);
(async function test() {
try {
await sequelize.sync({ force: true });
// seed
await MatterResource.bulkCreate([
{ attached_resources: [{ public_id: 1 }] },
{ attached_resources: [{ public_id: 2 }] },
{ attached_resources: [{ public_id: 3 }] },
]);
// test
const resource = await MatterResource.destroy({
where: {
attached_resources: {
[Op.contains]: [{ public_id: 1 }],
},
},
});
console.log(resource);
} catch (error) {
console.log(error);
} finally {
sequelize.close();
}
})();
检查数据库:
node-sequelize-examples=# select * from matter_resource;
id | attached_resources
----+------------------------
2 | {"{\"public_id\": 2}"}
3 | {"{\"public_id\": 3}"}
(2 rows)
执行脚本的日志:
Executing (default): DROP TABLE IF EXISTS "matter_resource" CASCADE;
Executing (default): DROP TABLE IF EXISTS "matter_resource" CASCADE;
Executing (default): CREATE TABLE IF NOT EXISTS "matter_resource" ("id" SERIAL , "attached_resources" JSONB[] DEFAULT ARRAY[]::JSONB[], PRIMARY KEY ("id"));
Executing (default): SELECT i.relname AS name, ix.indisprimary AS primary, ix.indisunique AS unique, ix.indkey AS indkey, array_agg(a.attnum) as column_indexes, array_agg(a.attname) AS column_names, pg_get_indexdef(ix.indexrelid) AS definition FROM pg_class t, pg_class i, pg_index ix, pg_attribute a WHERE t.oid = ix.indrelid AND i.oid = ix.indexrelid AND a.attrelid = t.oid AND t.relkind = 'r' and t.relname = 'matter_resource' GROUP BY i.relname, ix.indexrelid, ix.indisprimary, ix.indisunique, ix.indkey ORDER BY i.relname;
Executing (default): INSERT INTO "matter_resource" ("id","attached_resources") VALUES (DEFAULT,ARRAY['{"public_id":1}']::JSONB[]),(DEFAULT,ARRAY['{"public_id":2}']::JSONB[]),(DEFAULT,ARRAY['{"public_id":3}']::JSONB[]) RETURNING *;
Executing (default): DELETE FROM "matter_resource" WHERE "attached_resources" @> ARRAY['{"public_id":1}']::JSONB[]
1
更多推荐
如何基于Sequelize(postgres)中JSONB数组的属性删除资源?
发布评论