Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trying to query postgres JSON data type with sequelize

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
like image 230
ceckenrode Avatar asked Jun 07 '16 15:06

ceckenrode


People also ask

Can you use Sequelize with Postgres?

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.

What is the difference between JSON and Jsonb?

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.

How do I query with Sequelize?

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


1 Answers

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

  }
});
like image 171
ceckenrode Avatar answered Oct 24 '22 14:10

ceckenrode