查询对象数组（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',
    ],
]

我是 Laravel 和 Postgres 的新手，所以我想做一些基本的查询。

这里有一些有效的查询： （json 是列的名称）

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

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

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

现在我想要检查 drink beer 是否在 skills 中，以下代码无法实现此功能：

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

我这里遇到了语法错误：

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

我该如何搜索“喝啤酒”？

- Philipp Kühn

---

呃，听起来PDO把问号当作查询参数了...看看文档：位置参数：http://php.net/manual/en/pdostatement.bindparam.php 不知道该怎么解决。 - greg

是的，这似乎是问题所在。我已经在Postico中测试了一个原始查询，一切都正常。唉... - Philipp Kühn

那么 https://dev59.com/bmQo5IYBdhLWcg3wG8SE#16312053 呢？ - Florian Klein

Answer 1

如 Twitter 上 @tapoueh 所建议的那样，也许一个解决办法是使用运算符的基础函数：jsonb_exists(jsonb, text)。因此，您的查询将会是：

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

由@docteur_klein添加：链接到类似问题的页面。

- greg

---

缺少 )，请再次检查您的查询。 - Pratik Joshi

Answer 2

使用Laravel 5.6+，在JSON中进行搜索变得非常容易。

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

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

- 120DEV

---

您的链接是404。新的URL是https://laravel.com/docs/5.6/queries#json-where-clauses。 - Thykof

Answer 3

如果你需要进行LIKE搜索，那么就使用它：

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

- Stas Coder

Answer 4

如果您需要在对象数组中搜索值项，可以像这样转义字符串：

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

- Aline Matos