Sequelize (or clear SQL) query for selecting rows what includes value in JSON field?

Question

I have rows in my MYSQL and I Need Sequelize.js query.

Every row have col of type JSON what include this for example:

[
  {id: 1234, blah: "test"},
  {id: 3210, blah: "test"},
  {id: 5897, blah: "test"}
]

I have id and I need to select row what include this id in at least one object in array.

Answer 1

Raw mysql query will be like this:

SELECT * FROM `user` WHERE JSON_CONTAINS(`comments`, '{"id": 1234}');

Simple sequelize example:

const { fn, col, cast } = this.sequelize;

const User = this.sequelize.define('user', {  
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  comments: DataTypes.JSON,
  defaultValue: [],
})

User.findAll({
  where: fn('JSON_CONTAINS', col('comments'), cast('{"id": 1234}', 'CHAR CHARACTER SET utf8')),
})
.then(users => console.log('result', users.map(u => u.get())))
.catch(err => console.log('error', err));

Cast function is used to unescape double quotes around "id" to avoid wrong query string like this:

SELECT * FROM `user` WHERE JSON_CONTAINS(`comments`, '{\"id\": 1234}');

There is one more dirty mysql query example (don't use it):

SELECT * FROM `user` WHERE `comments` LIKE '%"id": 1234%';