CODEWITHSUNDEEP
postgresqllaravellaravel-5laravel-5.4postgresql-json
rap-2-h
rap-2-h

Reputation: 31948

JSON Where Clause on array of values with Query Builder

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?

Upvotes: 4

Views: 1614

Answers (1)

pozs
pozs

Reputation: 36214

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}]}')

Upvotes: 1

Related Questions