I have a newly created column in my table I need to query in one of my server controllers. This data in this column will be of JSON type and is named "meta". It will look something like this when completed
{
"audit": {"date": 1465311598315, "user": 20191932891}
}
currently all of the entries in this table have the empty JSON column with a NULL value.
In one of my server controllers I need to grab all entries where where meta is null or meta.audit.date is more than 90 days old. My query looks something like this
db.Question.findAll({
where: {
status: 'active',
PassageId: {
$eq: null
},
meta: {
audit: {
date: {
$or: {
$lte: threeMonthsAgo,
$eq: null
}
}
}
}
}
});
In this case three months ago is the date three months ago as a number.
No results are being returned and I get this error in my console:
Unhandled rejection SequelizeDatabaseError: operator does not exist: text <= bigint
Sequelize provides a comfortable API to work with PostgreSQL databases from setup to execution, but there are many ORMs (e.g. TypeORM, Objection. js) to choose from for a Node. js application if you want to expand your toolbelt.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
Sequelize instance comes with the query() method which you can use to run a raw query. The syntax of the method is as shown below: const [results, metadata] = await sequelize. query( "Your query here", { options } );
Turns out I wasn't using the $or operator correctly
db.Question.findAll({
where: {
status: 'active',
PassageId: {
$eq: null
},
{
$or: [{
'meta.audit.date': {
$eq: null
}
}, {
'meta.audit.date': {
$lte: threeMonthsAgo
}
}]
}
}
});
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With