Bookshelf.js where with json column Postgresql

Question

I have a problem with Bookshelf, I want use query to columns json type My table have columns 'data' type json, I want get all element with in this columns 'team' = 'PSG'

I test:

  collection.query('whereRaw', "data->'team'->>'PSG'");

I have this error

"argument of WHERE must be type boolean, not type text"

Or I test

collection.query('where', "data", "#>", "'{team, PSG}'");

I have this error

"The operator \"#>\" is not permitted"

I think that have a report with https://github.com/tgriesser/bookshelf/issues/550

Muhammad Raihan Muhaimin · Accepted Answer · 2016-04-29 01:02:00Z

4

Short answer:

collection.query('where', 'data', '@>', '{"team": "PSG"}');

Explanation:

Say you have a table foos, where an element foo is

 ------------------------
| id | attr              |
 ------------------------
| 0  |{ "bar": "fooBar"} | 
 ------------------------
| 1  |{ "bar": "fooFoo"} | 
 ------------------------

The raw query for that would be like.

select * from "foos" where "attr" @> '{"bar":"fooBar"}';

Now in Bookshelf if you have a model Foo that represent table foos it the query should be like.

Foo.where('attr', '@>', '{"bar":"fooBar"}').fetch().then(function(rows){});

Now for your case it should be like

collection.query('where', 'data', '@>', '{"team": "PSG"}');

Hopefully Zlatan Approves it.

edited Apr 29, 2016 at 1:02

answered Apr 26, 2016 at 22:14

Muhammad Raihan Muhaimin

5,7197 gold badges50 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vasanth Over a year ago

I'm using the same query, but still getting error,

Unhandled rejection error: select "foo".* from "foo" where "attr" @> $1 limit $2 - operator does not exist: json @> unknown

.

Muhammad Raihan Muhaimin Over a year ago

Can you please run your knex query with debug flag and post your exact query?

tabish89 Over a year ago

Whoever is trying to accomplish this same task, do not follow these instructions. Vasanth's comment from Oct 19 '16 still hold true. PostgreSQL generates the following error: ERROR: operator does not exist: json @> unknown LINE 1: ...t * from "users" where "email_subscription_prefs" @> '{"mark... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Collectives™ on Stack Overflow

Bookshelf.js where with json column Postgresql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related