Sequelize，MySQL - 使用 JSON 列值过滤表中的行 [英] Sequelize, MySQL - Filtering rows in table with JSON column values

问题描述

需要帮助了解如何使用 Sequelize 过滤 MySQL 表的 JSON 列中的带有嵌套值的行.文档没有它(仅适用于 PostgreSQL 和 MSSQL - ref)

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)

表定义-

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;
};

Comments 表中引用列的值 -

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

如何过滤orderItemId"为 2 的所有行.

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

预期的 SQL 查询

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

想出了一个使用sequelize.literal的方法，但是有没有办法不用这个函数.

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'

推荐答案

需要使用Sequelize.Op

示例:

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

Sequelize(运算符)

这篇关于Sequelize，MySQL - 使用 JSON 列值过滤表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！