如何基于Sequelize（postgres）中JSONB数组的属性删除资源？

数组的属性删除资源？"/>

如何基于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