Search multidimensional jsonb data in laravel postgres

Question

I have multiple jsonb data fields in my postgres database. Like my Contacts table has name, addresses, phones and emails as jsonb data fields. Most of the jsonb data fields are multidimensional. Like emails field have different json data for different email types. Following is the sample emails data i have in my database.

[
    {"tag": "Home", "value": "[email protected]"}, 
    {"tag": "Other", "value": "[email protected]"}, 
    {"tag": "Work", "value": "[email protected]"}, 
    {"tag": "Home", "value": "[email protected]"}
]

I want to execute a search on emails field's value data. Like i want to fetch all the contacts with email [email protected] in emails field.

I have tried to fetch the contacts with normal where query like

$contacts = Contact::where('emails->value', 'ilike', "%$query%")->get();

But it does not return anything and that is obvious because emails field is multidimensional. Although on a single dimensional json data this query works fine.

What is the proper way of searching such data fields in laravel eloquent ?. It will be better if it can be done without using whereRaw with jsonb_exists function.

hashmi · Accepted Answer · 2017-12-10 07:00:06Z

1

I got it working and posting the answer here if in case anyone needs help in future.

Basically we can do a simple where query with @> operator. And the value can be json encoded with the searched email. Following is the query to search for particular email without using whereRaw method.

$contacts = Contact::where("emails", '@>', '[' . json_encode(['value' => $query]) . ']')->get();

Hope it helps some one else in future :)

edited Dec 10, 2017 at 7:00

answered Dec 6, 2017 at 11:27

hashmi

6612 gold badges10 silver badges24 bronze badges

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

1 Comment

Md. Azahar Alam Over a year ago

I have the same formatted JSON. But it still returns nothing,I queried like this..... MeetingInvitee::where("apartments", '@>', '[' . json_encode(['flat_no' => 'A1']) . ']')->get()

Collectives™ on Stack Overflow

Search multidimensional jsonb data in laravel postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related