Sequelize, MySQL - Filtering rows in table with JSON column values

Question

Need help in figuring out how to filter rows in MySQL table's JSON column with nested values using Sequelize. Documentation doesn't have it (Only given for PostgreSQL & MSSQL - ref)

Table definition -

module.exports = (sequelize, DataTypes) => {
  const Comment = sequelize.define('Comment', {
    action: DataTypes.STRING,
    type: DataTypes.STRING,
    reason: DataTypes.STRING,
    reference: DataTypes.JSON,
    active: {
      type: DataTypes.INTEGER,
      defaultValue: 1,
    },
  }, {
    classMethods: {
      associate(models) {
        Comment.belongsTo(models.User, {
          foreignKey: 'userId',
        });
      },
    },
  });
  return Comment;
};

Values of reference column in Comments table -

{
  "orderItemId": 2,
  "xkey": 3,
  "ykey": 4
}
{
  "orderItemId": 4,
  "xkey": 1,
  "ykey": 1
}
{
  "orderItemId": 3,
  "xkey": 1,
  "ykey": 6
}
{
  "orderItemId": 2,
  "xkey": 1,
  "ykey": 0
}

How do I filter all the rows where "orderItemId" is 2.

Expected SQL query

select * from Comments where reference->"$.orderItemId" = 2

Figured out a way using sequelize.literal, but is there a way of not using this function.

models.Comment.findAll({
  where: sequelize.literal(`reference->"$.orderItemId"=2`),
})

How to add multiple conditions in the above case like -

reference->"$.orderItemId" = 2 and action = 'xyz'

Answer 1

You can use with below.

Comment.findAll({
  action:'xys',
  'reference.orderItemid':2
})

// oR

Comment.findAll({
  action:'xys',
  reference:{
  orderItemId:2
  }
})

Answer 2

Need to use Sequelize.Op

Example:

models.Comment.findAll({
    where: {
        action: 'xyz',
        [Op.and]: sequelize.literal(`reference->"$.orderItemId"=2`),
    },
});

Sequelize (Operators)

Answer 3

Try this:-

reference: {
              [Op.and]: [{orderItemId: {[Op.eq]: 2}}]
           }