Query array of objects (Postgres with JSONB, Laravel 5)

Question

I've got this data in a JSONB field:

[
    'first_name' => 'Philipp',
    'last_name' => 'Kühn',
    'size' => 170,
    'hobbies' => [
      'daily' => 'beer',
    ],
    'skills' => [
      'drink beer',
      'drink more beer',
    ],
]

I'm pretty new to Laravel and Postgres so I wanted to do some basic queries.

Here are some queries that works fine: (json is the name of the column)

$users = User::whereRaw("json ->> 'first_name' = 'Philipp'")->get();

$users = User::whereRaw("json ->> 'size' > '160'")->get();

$users = User::whereRaw("json #>> '{hobbies, daily}' = 'beer'")->get();

Now I want to check if drink beer is in skills and the following code doesn't work:

$users = User::whereRaw("json -> 'skills' ? 'drink beer'")->get();

Here I got a syntax error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"
LINE 1: select * from "users" where json ->> 'skills' $1 'drink beer...
^ (SQL: select * from "users" where json ->> 'skills' ? 'drink beer')

How can I search for drink beer?

Answer 1

As suggested by @tapoueh on twitter, maybe a work around would be to use the operator's underlying function: jsonb_exists(jsonb, text). So your query would be

$users = User::whereRaw("jsonb_exists(json -> 'skills', 'drink beer')")->get();

Added by @docteur_klein : A link to a similar problem.

Answer 2

With Laravel 5.6+ searching in a JSON becomes really easy

https://laravel.com/api/5.6/Illuminate/Database/Query/Builder.html#method_whereJsonContains

User::WhereJsonContains('json->skills', 'drink beer')->get();

Answer 3

If you need LIKE search, then use that:

\DB::table('users')
    ->whereRaw("json::json->>'skills' like '%drive a car%'")
    ->get();

Answer 4

If you need to search an value item inside array of objects, you can escape strings like this:

$query = "column @> '{\"INDEX_OF_ARRAY\":[{\"INDEX_OF_OBJECT\": \"".$searched_value."\"}]}'";
$colection = YourModel::whereRaw($query);
$colection->get();