选择，其中JSON数组包含 [英] Select, where JSON Array contains

问题描述

因此在Laravel 5中，有一个方便的东西叫做 JSON Where子句，它使用MySQL的新功能来存储和获取存储在列中的JSON:

So in Laravel 5 there's the handy thing called JSON Where Clauses using MySQL's new ability to store and fetch JSON stored in a column:

User::where('meta->colors', 'red')->get()

将返回所有行，其中列meta中的colors将设置为red.

would return all rows, where colors in the column meta would be set to red.

现在让我们说colors不是字符串，而是包含多种颜色(colors => ['red', 'blue', 'green'])的数组.

Now let's say colors is not a string, but an array containing multiple colors (colors => ['red', 'blue', 'green']).

什么是检索所有行的有效方法，其中colors包含例如值red?

What would be an efficient way to retrieve all rows, where colors contains e.g. the value red?

推荐答案

JSON_CONTAINS() does exactly what you're looking for:

JSON_CONTAINS(target, candidate[, path])

JSON_CONTAINS(target, candidate[, path])

通过返回1或0来指示给定的候选JSON文档是否包含在目标JSON文档中，或者(如果提供了路径参数)是否在目标内的特定路径上找到了候选对象. — 12.16.3搜索JSON值的函数

当前，Laravel的查询生成器未提供相应的API.不过，有一个开放内部建议书.

Currently, Laravel's query builder does not provide a corresponding API. There's an open internals proposal for it though.

同时，您可以执行原始查询:

In the meantime, you can execute a raw query:

\DB::table('users')->whereRaw(
    'JSON_CONTAINS(meta->"$.colors", \'["red"]\')'
)->get();

这将返回在其meta->colors JSON字段中具有红色"的所有用户.请注意， ->运算符需要MySQL 5.7.9 +.

Which would return all users that have "red" in their meta->colors JSON field. Note that the -> operator requires MySQL 5.7.9+.

您也可以直接在Eloquent模型上调用whereRaw().

You can also call the whereRaw() directly on an Eloquent model.

从5.6版本开始，Laravel的查询生成器包含一个新的 whereJsonContains 方法.

As of the 5.6 release, Laravel's query builder contains a new whereJsonContains method.

这篇关于选择，其中JSON数组包含的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！