Laravel Eloquent如何使用Where In查询JSON列？

Question

我在查询JSON列时遇到了问题。

之前我的代码是：

$query->whereIn('user', $users);

现在我已经将数据库类型更改为JSON列，并正在尝试修改新版本的代码。

在原始MYSQL中，这个可以工作。

JSON_CONTAINS(user, '"tom","Bill"') 

但是，当我将其放入 Eloquent/PHP 中时，它一直失败或者没有返回值。在这里，我传递了一个 User 数组，之前使用 WhereIn 在原始 SQL 中可以正常工作。

$leads->whereRaw("JSON_CONTAINS(user, '"$users"')")

有什么办法可以在Laravel中使其工作，以便我可以传递一个字符串数组，并将其与包含字符串数组的json列进行查询。

我的Json列的数据如下所示。

["Paul", "Tom", "Bob"]

- Logon

---

你不需要像这样添加吗？ $leads->whereRaw("JSON_CONTAINS(user, '" .$users ."')") - dns_nx

刚刚尝试了一下，返回了500，执行的查询是JSON_CONTAINS(user, 'Tom'')，并且显示语法错误或访问违规：1064，你的SQL语法有误，请检查与你的MySQL服务器版本相对应的手册以获取正确的语法使用方法，在第1行附近出现了'Tom''。 - Logon

有什么想法吗？我真的卡在这里了。 - Logon

Answer 1

MySQL 期望一个 JSON 字符串：

$leads->whereRaw('JSON_CONTAINS(user, ?)', [json_encode($users)])

在 Laravel 5.6.24 中，您可以使用 whereJsonContains()：

$leads->whereJsonContains('user', $users)

- Jonas Staudenmeir

Answer 2

如果$users是一个名字数组，那么你应该对其进行迭代并添加orWhereRaw条件，注意orWhereRaw可以接受一个参数数组：

 $users = ["Tom", "Bob"];

 foreach( $users as $user) {
      $leads->orWhereRaw("JSON_CONTAINS(user, ?)", [$user]);
 }

 $leads = $leads->get();

- YouneL

---

当我运行这个程序时，返回一个空数组。在这种情况下，问号代表什么？ - Logon

? 代表 $user 参数，类似于 PDO 准备语句，不能在 DB::raw 中直接连接 $user，因为这可能导致 SQL 注入攻击。 - YouneL

你尝试过使用 $leads->whereRaw("JSON_CONTAINS(user, ?)", [$users]); 和 $users = '"tom","Bill"' 吗？ - YouneL

这个SQL语句运行正常：where JSON_CONTAINS(user, '"Paul"')但是代码返回了一个空数组：foreach($users as $u){ $query->whereRaw("JSON_CONTAINS(user, ?)", [$u]); die(var_dump($query->get())); } - Logon

你必须使用 orWhereRaw 而不是 whereRaw，我认为这就是为什么你得到空数组的原因。 - YouneL

Answer 3

你不一定要使用whereRaw。假设下面的data是json列：

$categories = ['category-one', 'category-two', 'category-three'];

Article::where('published', true)
    ->where(function ($query) use ($categories) {

        foreach ($categories as $category) {
            $query->orWhereJsonContains('data->categories', $category);
        }
    })
    ->get();

- Bennett