I have Node server that uses MariaDB (MySQL) via Sequelize.
I have some rows in my db that looks like this, for example:

The "clips" column is a string, but in an array structure.

My task is to find a way to remove specific videoId from the clips column. For example, if the API route gets the Id "videoId123456", it will be deleted from rows 1,2,4. If it gets the Id "videoId8910111213" it will be deleted from row 1.

I know how to do it by pure MySQL, something like:

UPDATE player
SET clips = REPLACE(clips ,'"videoId1234567"','')
WHERE clips LIKE '%"videoId1234567"%';

I know I can run pure SQL commands with Sequelize as string like:

await sequelize.query(
`   UPDATE player
    SET clips = REPLACE(clips ,'"videoId1234567"','')
    WHERE clips LIKE '%"videoId1234567"%';
`, {
  type: QueryTypes.UPDATE
});

But I wonder if there any way to do it by pure Sequelize, something like:

player.update({
  'clips': 'clips.replace('"videoId1234567"','')'
   where: { 'clips': [Op.like]: '"videoId1234567"' }
});

Thanks.

CodePudding user response：

You can use Sequelize.fn amd Sequelize.col to achieve the goal to use the replace SQL function:

player.update({
  clip: Sequelize.fn('replace', Sequelize.col('clip'), '"videoId1234567"','')
}, {
   where: { clip: { [Op.like]: '"videoId1234567"' } }
});

P.S. It would be better not to just replace substrings in this JSON-like array and prefer using SQL functions that can work with JSON to work with an array (if it's applicable to MariaDB).