laravel 选择，其中 JSON 数组包含
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46055223/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select, where JSON Array contains
提问by Elwin
So in Laravel 5 there's the handy thing called JSON Where Clausesusing MySQL's new ability to store and fetch JSON stored in a column:
所以在 Laravel 5 中有一个叫做JSON Where Clauses的方便的东西，它使用 MySQL 的新功能来存储和获取存储在列中的 JSON：
User::where('meta->colors', 'red')->get()
would return all rows, where colorsin the column metawould be set to red.
将返回所有行，colors列meta中将设置为red.
Now let's say colorsis not a string, but an array containing multiple colors (colors => ['red', 'blue', 'green']).
现在让我们说colors不是一个字符串，而是一个包含多种颜色 ( colors => ['red', 'blue', 'green'])的数组。
What would be an efficient way to retrieve all rows, where colorscontains e.g. the value red?
检索所有行的有效方法是什么，其中colors包含例如值red？
回答by sepehr
JSON_CONTAINS()does exactly what you're looking for:
JSON_CONTAINS()完全符合您的要求：
JSON_CONTAINS(target, candidate[, path])Indicates by returning 1 or 0 whether a given candidate JSON document is contained within a target JSON document, or—if a path argument was supplied—whether the candidate is found at a specific path within the target. — 12.16.3 Functions That Search JSON Values
JSON_CONTAINS(target, candidate[, path])通过返回 1 或 0 来指示给定的候选 JSON 文档是否包含在目标 JSON 文档中，或者——如果提供了路径参数——是否在目标内的特定路径中找到了候选。— 12.16.3 搜索 JSON 值的函数
Currently, Laravel's query builder does not provide a corresponding API. There's an open internals proposalfor it though.
目前，Laravel 的查询构建器没有提供相应的 API。不过，有一个公开的内部提案。
In the meantime, you can execute a raw query:
同时，您可以执行原始查询：
\DB::table('users')->whereRaw(
'JSON_CONTAINS(meta->"$.colors", \'["red"]\')'
)->get();
Which would return all users that have "red" in their meta->colorsJSON field. Note that the ->operatorrequires MySQL 5.7.9+.
这将返回在其meta->colorsJSON 字段中具有“红色”的所有用户。请注意，该->运算符需要 MySQL 5.7.9+。
You can also call the whereRaw()directly on an Eloquent model.
您也可以whereRaw()直接在 Eloquent 模型上调用。
Laravel 5.6
Laravel 5.6
As of the 5.6 release, Laravel's query builder contains a new whereJsonContainsmethod.
从 5.6 版本开始，Laravel 的查询构建器包含一个新whereJsonContains方法。
回答by Elwin
I think a way would be using the likeoperator:
我认为一种方法是使用like运算符：
User::where('meta->colors', 'like', '%"red"%')
However, this would only work if the values never contain the character "and the delimiters wouldn't change.
但是，这仅在值从不包含字符"且分隔符不会更改时才有效。
回答by alvaro.canepa
An update for this answer, according to MySQLor MariaDb, the correct syntax must be JSON_CONTAINS(@json, 'red', '$.colors'), and is necessary to use JSON_EXTRACT.
这个答案的更新，根据MySQL或MariaDb，正确的语法必须是JSON_CONTAINS(@json, 'red', '$.colors')，并且必须使用JSON_EXTRACT。
So them, the code inside Laravel (for version 5.5 or less).
所以他们，Laravel 内部的代码（5.5 或更低版本）。
Like say @Elwin,
metacolumn must contains the following JSON:{ "colors": ["red", "blue", "green"] }
就像@Elwin一样，
meta列必须包含以下 JSON：{ "colors": ["red", "blue", "green"] }
User::whereRaw("JSON_CONTAINS(JSON_EXTRACT(meta, '$.colors'), '\"{$color}\"')")
Remember to use double quotes in value sentence.JSON_CONTAINS(JSON_EXTRACT(meta, '$.colors'), '"red"')
记住在值语句中使用双引号。JSON_CONTAINS(JSON_EXTRACT(meta, '$.colors'), '"red"')
回答by Sreejith BS
The whereInmethod verifies that a given column's value is contained within the given array.
Try this:
该whereIn方法验证给定列的值是否包含在给定数组中。尝试这个：
$colorArray = ['red', 'blue', 'green'];
$user = User::whereIn($meta->color, $colorArray)->get();
More about Laravel's whereIn.
更多关于Laravel 的 whereIn。
