Level 7
you can try this https://laravel.com/docs/5.5/queries#json-where-clauses
Jan 25, 2018
6
Level 1
Working with Postgres JSONB in Laravel 5.5
Here's our table schema
Schema::create('contacts', function(Blueprint $table)
{
$table->uuid('id')->default(\DB::raw('gen_random_uuid()'))->comment('primary key');
$table->string('email', 100)->nullable()->comment('email address');
$table->jsonb('data')->nullable()->comment('Any params go here');
$table->timestamps();
});
Here's an insert statement in Tinker
Contact::create(['email'=>'[email protected]', 'data'=>'{"first_name":"Geoff","info":{"id":1234123,"groups":[101,202,303]}}'])
Here's a sample query
Contact::where('data->first_name', 'Geoff')->first()
App\Models\Contact {#890
id: "8eeab075-57ae-4b29-8aca-df8f7c296dd4",
email: "[email protected]",
data: "{"first_name": "Geoff", "info": {"id": 1234123, "groups": [101, 202, 303]}}",
created_at: "2018-01-25 18:04:03",
updated_at: "2018-01-25 18:04:03",
}
So far so good.
How do I find contacts that belong to group 202?
Level 1
I saw that, but I don't want to pass the whole list of ids, eg [101,202,303] because the where clause is using the "=" operator.
I wasn't able to get anything but null when this->that->other json field is a list/array.
Trying to figure a way to query Contact::where('data->info->groups' .... contains 202)
Level 67
Look at whereIn()
Level 1
whereIn() does not work that way. You're thinking of it backwards.
I don't want to provide a list of possible values, I want to provide a specific value that an array of values in JSONB node may contain.
data->info->groups returns [101, 202, 303]
I want a query where I find all the records where in_array(202, data->info->groups)
I don't think it's possible in L5.5. I don't even know if it's possible in a Postgres SQL query.
Level 10
Old, I know, but if I found this, others will too =)
@phpguru, in postgresql you would use the following syntax:
SELECT * FROM contacts WHERE data->'info'->groups @> '[202]'::jsonb;
I'm trying to work this out in eloquent still myself... but now with that syntax, we'll see if I can get there. I've already written a helper class to add support for Postgresql's DistinctOn functionality: https://packagist.org/packages/datajoe/postgresql-distinct-on
If I cannot find pre-built functionality, I'll work on a jsonb extension as well... hoping I don't have to though!
Level 10
Found it!!!
@phpguru ... really @anyone_else_trying_to_figure_this_out:
Contact::whereJsonContains('data->info->groups', 202)->get();
Please or to participate in this conversation.