New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Querying Postgres jsonb column contaning non-text type of fields cause "operator does not exist" exception #3824

Closed
xwk opened this Issue May 29, 2015 · 1 comment

Comments

2 participants
@xwk

xwk commented May 29, 2015

Suppose model Store has a column called 'jsonb' which has type jsonb. Query like

Store.find({
      where: {
        jsonb: {
          enabled: true
        }
      }

will cause SequelizeDatabaseError: operator does not exist: text = boolean exception.

The reason for that is Sequelize will translate the query into following SQL

SELECT "id", "jsonb", "createdAt", "updatedAt" FROM "stores" AS "store" WHERE ("store"."jsonb"#>>'{enabled}') = true LIMIT 1.

Notice #>> operator is used, which converts the jsonb type to text. The correct SQL should be

SELECT "id", "jsonb", "createdAt", "updatedAt" FROM "stores" AS "store" WHERE ("store"."jsonb"#>'{enabled}') = 'true'::jsonb LIMIT 1;

Full sample code as below

var Sequelize, Store, configs, sequelize;

Sequelize = require("sequelize");

configs = require("../configs");

sequelize = new Sequelize(configs.DB_NAME, configs.DB_USERNAME, configs.DB_PASSWORD, {
  host: configs.DB_HOSTNAME,
  dialect: 'postgres'
});

Store = sequelize.define('store', {
  jsonb: Sequelize.JSONB
});

Store.sync({
  force: true
}).then(function() {
  return Store.create({
    jsonb: {
      name: 'test store',
      enabled: true,
      grade: 3
    }
  }).then(function() {
    return Store.find({
      where: {
        jsonb: {
          enabled: true
        }
      }
    });
  })

weikai-booodl added a commit to xwk/sequelize-fixtures that referenced this issue May 29, 2015

Skip the check for whether the row has been in the database
this is intended as a temporary workaround for the problem caused by
sequelize/sequelize#3824, which resulted in
inserting jsonb column containing non-text type impossible.
@mickhansen

This comment has been minimized.

Contributor

mickhansen commented May 29, 2015

("store"."jsonb"#>'{enabled}')::boolean = true might work aswell? That's the technique we use for numbers

@mickhansen mickhansen added the bug label May 29, 2015

@stale stale bot added the stale label Jun 29, 2017

@stale stale bot closed this Jul 7, 2017

@yonjah yonjah referenced this issue Aug 7, 2017

Merged

Simplify where item query #8068

4 of 5 tasks complete

janmeier added a commit that referenced this issue Sep 18, 2017

fix(query-generator) Simplify where item query (#8068)
* refactor(abstruct.query-generator): Reduce complexity of abstruct query-generator whereItemQuery by

whereItemQuery had cyclic complexity of 85 reduced to 26 to make logic a bit easier to follow.
Logic is almost identical but at places where logic looked like an obvoius error it was tweeked, like -
	_traverseJSON had weird logic if the item was plain object -
		cast wast set by either path (ok) or the first value of the first property of an object (doesn't make sense)
		where value passed into whereItemQuery was in the main function scope causing it to accumulate properties on each iteration (doesn't make sense)
All test are passing with no issues but since there were minor logic changes there might be some edge cases needed to be addressed.
Though it's more resonable to assume changes will fix bugs related to those edge cases than cause them

* fix(abstract.query-generator): Fix discrepancies between postgres and sqlite casting and handling of

postgres and sqlite handle casting and JSONB formats a bit diffrently yet some previous commits
didn't took this changes into consideration when handling casting and didn't test casting properly

* fix(abstract.query-generator): Fix issue where not properly treating  plain js object values for JSON column

Fixes #3824 (again)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment