SequelizeでJSONカラムを扱う

MySQLのJSONのdata typeをSequelizeで使ってみました。

MySQLのバージョンは5.7.24を使用しています。

テーブル構成

infoの中にはこの様なデータを入れています。

マイグレーションファイル

JSONのカラムのtypeにはSequelize.JSONを指定します。

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Books', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      title: {
        type: Sequelize.STRING
      },
      info: {
        type: Sequelize.JSON
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Books');
  }
};

初期データ

検索

JSONデータ内のisbn10に対して検索してみます。

db.Book.findOne({where: {
    "info.isbn10": "4167178060"
  }
}).then((books)=>{
  console.log(books.dataValues)
}).then(()=>{
  db.sequelize.close()
})

SQL

SELECT `id`, `title`, `info`, `createdAt`, `updatedAt` FROM `Books` AS `Book` WHERE (`Book`.`info`->>'$."isbn10"') = '4167178060' LIMIT 1;

実行結果

{ id: 1,
  title: '青春を山に賭けて',
  info:
   { page: 297, isbn10: '4167178060', isbn13: '978-4167178062' },
  createdAt: 2019-03-30T08:31:04.000Z,
  updatedAt: 2019-03-30T08:31:04.000Z }

JSONのカラムはdictionary形式で返ってくるのでこのようにアクセスできます。

books.dataValues.info.page // 297

特定の値のみ更新

JSONデータのアップデート時は以下のように実行すると他のJSONデータが消えてしまうので

db.Book.update({
  "info.isbn10": "xxxxx"
}, {
  where: {id: 1}
}).then(()=>{
  db.sequelize.close()
})

このように指定してMySQLのJSON_SET関数を呼び出して指定の項目のみ更新します。

db.Book.update({
  info: db.sequelize.fn("JSON_SET", db.sequelize.col("info"), "$.isbn", "xxxxx")
}, {
  where: {id: 1}
}).then(()=>{
  db.sequelize.close()
})

SQL

UPDATE `Books` SET `info`=JSON_SET(`info`, '$.isbn', 'xxxxx'),`updatedAt`=? WHERE `id` = ?

実行後データ

削除

検索の場合と同じように指定できます。

db.Book.destroy({
  where: {"info.isbn10": "xxxxx"}
}).then(()=>{
  db.sequelize.close()
})

SQL

DELETE FROM `Books` WHERE (`info`->>'$."isbn10"') = 'xxxxx'

実行後データ