CODEWITHSUNDEEP
node.jssequelize.js
user2398517
user2398517

Reputation: 23

Sequelize: Storing dates in JSON columns and comparing them to dates in where clauses

I am using PostgreSQL and sequelize. I have a model column "checkedAt" for storing multiple dates. I want to be able to compare the different dates from the column data.

The issue with this is that the dates are stored as strings since it is not a Date but a JSON DataType.

Here is how I defined the model

const Notification = sequelize.define("Notification", {
    checkedAt: {
      type: DataTypes.JSON,
      defaultValue: {
        bySomeJob: new Date(),
        bySomeOtherJob: new Date()
      }
    }
});

how I try to query with no success

const someDate = new Date();

await db.Notification.findAll({
   where: {
     checkedAt: {
        bySomeJob: {
            $lte: someDate
        }
     }
   }
});

I've used this method before for integers and booleans but not with a date.

I found this function to turn strings into date format 

sequelize.fn('date', sequelize.col('checkedAt'))

But not sure if and how could it be used for a property of the column.

I expect to be able to query the model by comparing a Date object to a transformed value from the property of a JSON object column.

Upvotes: 2

Views: 942

Answers (1)

Trevor Johnson
Trevor Johnson

Reputation: 936

If you need to associate multiple dates to a notification, then you may be better off creating a new model used specifically for storing checkedAt dates, and creating an association between notifications and checkedAt dates. Something like Notification.hasMany(Dates);

This way when you query for notifications you can include the Dates table, and then be specific about what dates to include from that table, like with using $lte, which is what you were trying.

It might look something like this

await db.Notification.findAll({
    include: [
        {
            model: db.Date,
            where: {
                checkedAt: {
                    bySomeJob: {
                        $lte: someDate
                    }
                }
            }
        }
    ]
});

Upvotes: 1

Related Questions