Sequelize: use $like on JSON data type with PostgreSQL

Question

I'm using Sequelize with PostgreSQL.

I need to build a destroy query that can use the $like operator on a JSON data type.

For example, let's take this model definition:

var Sequelize = require('sequelize');
var sequelize = new Sequelize('database', 'username', 'password');

var Project = sequelize.define('project', {
  name: Sequelize.STRING,
  details: {
    type: Sequelize.JSON,
    defaultValue: {}
  },
});

Now, I would like to be able to run a query to destroy all Project entries that don't have any details:

Project.destroy({
  where: {
    details: {
      $like: '{}'
    }
  }
})

But I get this error:

[SequelizeDatabaseError: operator does not exist: json ~~ unknown]

So, is there any way to do this and how?

Thanks!!

Answer 1

Try to use:

$like: { $any: ['cat', 'hat']} // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike

From docs.