在Laravel Postgres中搜索多维jsonb数据 [英] Search multidimensional jsonb data in laravel postgres
问题描述
我的postgres数据库中有多个jsonb数据字段.就像我的Contacts表具有name, addresses, phones and emails作为jsonb数据字段一样.大多数jsonb数据字段都是多维的.像emails字段对于不同的电子邮件类型具有不同的json数据.以下是我在数据库中拥有的示例电子邮件数据.
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": "foo@bar.com"},
{"tag": "Other", "value": "john@doe.net"},
{"tag": "Work", "value": "john@foo.com"},
{"tag": "Home", "value": "foo@gmail.com"}
]
我想对电子邮件字段的值数据执行搜索.就像我想通过电子邮件字段中的电子邮件john@doe.net获取所有联系人.
I want to execute a search on emails field's value data. Like i want to fetch all the contacts with email john@doe.net in emails field.
我试图通过正常的查询方式来获取联系人
I have tried to fetch the contacts with normal where query like
$contacts = Contact::where('emails->value', 'ilike', "%$query%")->get();
但是它不返回任何东西，这很明显，因为电子邮件字段是多维的. 尽管在一维json数据上，该查询也能正常工作.
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.
在laravel eloquent中搜索此类数据字段的正确方法是什么?如果可以在不使用whereRaw和jsonb_exists功能的情况下完成 会更好.
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.
推荐答案
如果以后有人需要帮助，我会使其工作并在此处发布答案.
I got it working and posting the answer here if in case anyone needs help in future.
基本上，我们可以使用@>运算符进行简单的where查询.并且该值可以使用搜索到的电子邮件进行json编码.以下是不使用whereRaw方法搜索特定电子邮件的查询.
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 :)
这篇关于在Laravel Postgres中搜索多维jsonb数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
