Bookshelf.js where with json Postgresql column

Question

I have a problem with Bookshelf, I want to use a query on columns like json. My table has a data type of columns json, I want to get all the elements in this column "team" = "PSG"

I am testing:

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

I have this error

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

Or am I testing

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

I have this error

"Operator \" #> \ "not allowed"

I think you 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 foos table where the element foo is

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

A raw request for this will be similar.

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

Now in the Bookshelf, if you have a Foo model representing the foos table, it should look similar.

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

Now for your case it should be like

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

I hope Zlatan approves of this.