JSON Where Clause on array of values with Query Builder

Question

Here is a JSON value in a data column of a things table:

{a: [{b: 1}, {b: 2}]}

I can get all things containing a b that is equals to 1 with a raw query like this:

select * from things where data @> '{ "a": [{"b": 1}] }';

I know we can run query with Laravel with JSON where clause with Laravel: https://laravel.com/docs/5.4/queries#json-where-clauses. I can write something like:

Thing::where('a->c', 'foobar');

But can I write a where to check if a contains {b: 1} just like in the raw query with Laravel's Query Builder?

It seems that Laravel operates with the -> and ->> operators in these "JSON where clauses" (which is not what you want to achieve). But the PostgresGrammar supports the @> and <@ operators directly, so (in theory) you could write ->where('data', '@>', '{"a":[{"b":1}]}') — pozs
– pozs, Commented May 5, 2017 at 9:25
@pozs I have just checked and your "theory" works! Thank you very much I did not think about this, I was writing a whereRaw query. You can post your comment as an answer and I will accept it :) — rap-2-h
– rap-2-h, Commented May 5, 2017 at 9:29

pozs · Accepted Answer · 2017-05-05 09:31:44Z

1

Laravel (as of now) operates with the-> and ->> operators in these "JSON where clauses" (at least, for PostgreSQL). This is not what you want to achieve.

But the PostgresGrammar supports the @> and <@ operators directly, so you could write:

Thing::where('data', '@>', '{"a":[{"b":1}]}')

answered May 5, 2017 at 9:31

pozs

36.6k5 gold badges61 silver badges66 bronze badges

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

Collectives™ on Stack Overflow

JSON Where Clause on array of values with Query Builder

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related