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

Question

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'

Asghar Musani · Accepted Answer · 2019-05-07 03:55:11Z

16

Need to use Sequelize.Op

Example:

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

Sequelize (Operators)

edited May 7, 2019 at 3:55

Asghar Musani

6085 silver badges23 bronze badges

answered Sep 12, 2018 at 8:18

Uladzislau Vavilau

9619 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Himavanth Over a year ago

Thanks a lot! Worked perfectly. Also, anyway to remove the use of literal?

Uladzislau Vavilau Over a year ago

Can be used sequelize.col sequelize.where(sequelize.col('profile->user.first_name'), { [Op.like]: sequelizeFilters.search, }),

Uladzislau Vavilau Over a year ago

Did you like my answer? If so, rate it.

Wisnu Over a year ago

This worked perfectly for object, but how with array object? for example: [{"status": "y", "user_id": 6}, {"status": "n", "user_id": 3}] I've try with code above did't work.

Mahir Altınkaya · Accepted Answer · 2021-03-06 14:37:42Z

4

You can use with below.

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

// oR

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

answered Mar 6, 2021 at 14:37

Mahir Altınkaya

4396 silver badges17 bronze badges

Comments

Ayman Elshehawy · Accepted Answer · 2020-06-15 13:32:31Z

2

Try this:-

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

answered Jun 15, 2020 at 13:32

Ayman Elshehawy

3,01430 silver badges22 bronze badges

Comments

Lucio Mollinedo · Accepted Answer · 2022-08-30 17:42:44Z

0

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})`)
        ]

answered Aug 30, 2022 at 17:42

Lucio Mollinedo

2,4341 gold badge36 silver badges32 bronze badges

2 Comments

Kimball Robinson Over a year ago

It seems like this would open you up to injection attacks. Never directly insert a variable into a query clause.

Lucio Mollinedo Over a year ago

@KimballRobinson this code isn't inserting anything. It's using an array to filter the data. It is assumed that your data (in this case, the array of ids) is already sanitized.

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

Comments

Try this:-

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

Try this:-

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related