javascript - Sequelize, MySQL - Filtering rows in table with JSON column values - Stack Overflow

admin管理员组

文章数量:1200398

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)

Table definition -

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

Values of reference column in Comments table -

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

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

Expected SQL query

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

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'

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)

Table definition -

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

Values of reference column in Comments table -

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

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

Expected SQL query

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

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'

• javascript
• mysql
• json
• sequelize.js

Share Improve this question Follow edited Sep 12, 2018 at 8:08 Himavanth asked Sep 12, 2018 at 7:52 HimavanthHimavanth 41011 gold badge66 silver badges1515 bronze badges

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 16

Need to use Sequelize.Op

Example:

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

Sequelize (Operators)

You can use with below.

Comment.findAll({
  action:'xys',
  'reference.orderItemid':2
})

// oR

Comment.findAll({
  action:'xys',
  reference:{
  orderItemId:2
  }
})

Try this:-

reference: {
              [Op.and]: [{orderItemId: {[Op.eq]: 2}}]
           }

Expanding a bit on the answer provided by Uladzislau Vavilau, the following works to filter rows in a table where the value of a JSON property is within an array of values, and not just one value:

const orderItemIdsArray = [1, 2, 3, 4]

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

If the ids are UUID (string), you will need to enclose them with quotes before doing that:

const orderItemIdsArray = [
    "3ad108a4-f68c-4aca-9967-072ce5ec21af", 
    "ef30f7ca-b1bb-4e94-a06f-b6c783f5dc1f", 
    "bb591f28-0df9-4254-aa9b-c162fb0ed154"
]

const idsWithQuotes = orderItemIdsArray.map((id) => '"' + id + '"')

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

本文标签： javascriptSequelizeMySQLFiltering rows in table with JSON column valuesStack Overflow