CODEWITHSUNDEEP
javascriptmysqljsonsequelize.js
Himavanth
Himavanth

Reputation: 410

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

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'

Upvotes: 7

Views: 10444

Answers (4)

Lucio Mollinedo
Lucio Mollinedo

Reputation: 2424

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

Upvotes: 0

Mahir Altınkaya
Mahir Altınkaya

Reputation: 439

You can use with below.

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

// oR

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

Upvotes: 4

Ayman Elshehawy
Ayman Elshehawy

Reputation: 2964

Try this:-

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

Upvotes: 2

Uladzislau Vavilau
Uladzislau Vavilau

Reputation: 931

Need to use Sequelize.Op

Example:

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

Sequelize (Operators)

Upvotes: 16

Related Questions