Searching on JSON field in sails-postgres

[Dipankar Patro]

Hi all,

I am running sails 0.9.9. (This is going to be our production system)

I am trying to run a simple query for finding one user whose facebook id matches with the one provided.

var verifyFunction = function (accessToken, refreshToken, profile, done) {

  sails.log.debug("Passport :", profile.provider, "-->", profile.displayName);

  profile["aT"] = accessToken;

  profile["rT"] = refreshToken;

  process.nextTick(function () {

    var query = {};

    query[profile.provider + ".id"] = profile.id;

    User.findOne(query, function (err, user) {

      if (user) {

        // User is already registered

        return done(null, user);

      }

      else {

        // Profile is not found in Database.

        profile.notRegistered = true;

        return done(null, profile);

      }

    });

  });

};

The query is failing giving the following error in output.

debug: Passport : facebook --> Manish Kumar

debug: Lowering sails...

/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:285

      if(self._schema[key].type === 'text') caseSensitive = false;

                          ^

TypeError: Cannot read property 'type' of undefined

    at Object.sql.and (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:285:27)

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:256:19

    at Array.forEach (native)

    at Query.where (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:195:24)

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:119:14

    at Array.forEach (native)

    at Query._build (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:115:32)

    at Query.find (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/query.js:35:21)

    at __FIND__ (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/adapter.js:362:40)

    at after (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/adapter.js:506:7)

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/lib/adapter.js:492:7

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/node_modules/pg/lib/pool.js:55:9

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/node_modules/pg/node_modules/generic-pool/lib/generic-pool.js:278:11

    at /home/lucius/IQR/phoenix/node_modules/sails-postgresql/node_modules/pg/lib/pool.js:36:18

    at null.<anonymous> (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/node_modules/pg/lib/client.js:145:7)

    at g (events.js:180:16)

    at EventEmitter.emit (events.js:117:20)

    at null.<anonymous> (/home/lucius/IQR/phoenix/node_modules/sails-postgresql/node_modules/pg/lib/connection.js:97:12)

    at Socket.EventEmitter.emit (events.js:95:17)

    at Socket.<anonymous> (_stream_readable.js:745:14)

    at Socket.EventEmitter.emit (events.js:92:17)

    at emitReadable_ (_stream_readable.js:407:10)

    at emitReadable (_stream_readable.js:403:5)

    at readableAddChunk (_stream_readable.js:165:9)

    at Socket.Readable.push (_stream_readable.js:127:10)

    at TCP.onread (net.js:528:21)

Tried every other thing : by replacing the highlighted query to 

query[profile.provider + "->>id"] = profile.id;

Still not working..

[Mandeep Gulati]

To elaborate what Dipankar ( my co-worker) has mentioned here, consider a table in postgres

create table dummy ( id int not null, data json );

Then, if I query the table using the following query:

select * from dummy where data->'x' = 10;

Now since there are no records in the table yet and there is no such property as 'x' in any record, it should return zero results.

But I get the following error:

postgres=# select * from dummy where data->'x' = 10;
ERROR:  operator does not exist: json = integer
LINE 1: select * from dummy where data->'x' = 10;

However following query works:

select * from dummy where cast(data->>'x' as integer) = 10;

So, essentially one has to typecast to compare with an integer. 

We think the issue is somewhat related to the same only. And the table we are querying is empty. I am currently looking in the utils.js file for postgres to see if I can get this working. 

Please let me know if I am missing anything or proceeding in the wrong direction ?

Posted the same question on stackoverflow:

http://stackoverflow.com/questions/24043688/postgresql-using-json-sub-element-in-where-clause/24044453#24044453