4

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?

rap-2-h
  • 30,204
  • 37
  • 167
  • 263
  • It [seems that Laravel operates with](https://github.com/laravel/framework/blob/7212b1e9620c36bf806e444f6931cf5f379c68ff/src/Illuminate/Database/Query/Grammars/PostgresGrammar.php#L256) 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 May 05 '17 at 09: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 May 05 '17 at 09:29

1 Answers1

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}]}')
pozs
  • 34,608
  • 5
  • 57
  • 63