How to use MySQL JSON datatype with Sequelize

Using MySQL's JSON data type in Sequelize is a highly useful feature, especially when you need to store flexible data schemas or unstructured data. Here are some fundamental steps to define and work with JSON data types in Sequelize:

1. Define a model with a JSON field

When defining a model in Sequelize, specify the field type as Sequelize.JSON to store JSON data. The following example demonstrates how to define a JSON field in a model:

javascript
const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('mysql://user:password@localhost:3306/database');

const User = sequelize.define('User', {
  // Define a JSON field named 'settings'
  settings: {
    type: DataTypes.JSON,
    allowNull: true
  }
});

2. Insert and update records containing JSON data

After defining a model with a JSON field, you can insert or update records containing JSON data. For example:

javascript
// Insert new record
await User.create({
  settings: {
    theme: 'dark',
    notifications: true,
    language: 'zh-CN'
  }
});

// Update record
await User.update({ settings: { theme: 'light' } }, {
  where: {
    id: 1
  }
});

3. Query data containing JSON fields

You can use standard query methods to retrieve data with JSON fields. For more complex queries on JSON fields (such as filtering by specific properties), leverage Sequelize's JSON manipulation capabilities. For instance:

javascript
// Query all users whose settings language is Chinese
const users = await User.findAll({
  where: {
    'settings.language': 'zh-CN'
  }
});

console.log(users);

4. Use JSON functions

MySQL provides various JSON functions that can be utilized in Sequelize queries. For example, use JSON_EXTRACT to retrieve specific values from a JSON object:

javascript
const users = await User.findAll({
  attributes: [
    [sequelize.fn('JSON_EXTRACT', sequelize.col('settings'), '$.language'), 'language']
  ]
});

console.log(users);

Summary

With Sequelize's JSON data type, you can flexibly handle diverse data structures, particularly for storing and querying unstructured data. Ensure you leverage MySQL's JSON-related features to efficiently manage and query this data type.