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

Answer 1

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.