sequelize: find in json array

Question

In sequelize i have a model:

{
   modelId: {
      type:  DataTypes.UUID ,
      allowNull: false,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4 
    }
   name: DataTypes.STRING(1024),
   type: DataTypes.STRING,
   obj: DataTypes.JSON
}

and in DB, my obj is an array like this:

[{
   id: '123456',
   text: 'test',
   name 'xpto'
},{
   id: '32554',
   text: 'test2',
   name 'xpte'
},{
   id: '36201',
   text: 'test3',
   name 'xpta'
}]

i tried these:

btp.findAll({
        where: {
          obj:{
             [Op.contains]:[{id: req.body.id}]
          }
        },
        attributes: ['modelId','name','type','obj']
      })

but does not work, return this error:

{"name": "SequelizeDatabaseError",
"parent": {
    "name": "error",
    "length": 128,
    "severity": "ERROR",
    "code": "42704",
    "file": "parse_coerce.c",
    "line": "1832",
    "routine": "enforce_generic_type_consistency",
     "sql":"....."}

so, i need to find in database all entries have in obj, id: '123456'

my question is the same than this: https://github.com/sequelize/sequelize/issues/7349

but thats does not working for me, i need to return all entries that contains... i'm using "sequelize": "4.28.6", and "pg-hstore": "^2.3.2",

can any one help?

Answer 1

I'm not familiar with the specific error you're getting, but one potential issue is that you're using a JSON column instead of JSONB. In Postgres, JSON columns just store the raw JSON text, and so don't support the containment operator (@>) which is needed for Sequelize's "contains".

All you need to do to fix this is change the column definition in the model to:

obj: DataTypes.JSONB

The only other issue I can think of would be req.body.id having an invalid value. I'd suggest verifying that it's actually getting a valid ID string.

Beyond these 2 potential issues, the query you wrote should work.