使用laravel构建器的JSON列查询

Question

我在mysql中有一个json列，我正在尝试使用laravel-bulider查询该列。该列有一个json对象数组，我想查询该json对象中的Value属性。

use IlluminateDatabaseEloquentBuilder;
class SizeFilter
{
public function filter(Builder $builder, $value): Builder{
return $builder->whereJsonContains('sizes',[['Value' => $value]]);
}

我的json列结构是

[{"SizeID":34,"Sku":null,"Value":"10","stock":2},{"SizeID":35,"Sku":null,"Value":"12","stock":0},{"SizeID":36,"Sku":null,"Value":"14","stock":0},{"SizeID":37,"Sku":null,"Value":"16","stock":0},{"SizeID":38,"Sku":null,"Value":"18","stock":0},{"SizeID":32,"Sku":null,"Value":"6","stock":0},{"SizeID":33,"Sku":null,"Value":"8","stock":1}]

Answer 1

什么是$value？它一定是字符串。如果它是一个整数，JSON搜索将不起作用。

我没有Laravel，但我在MySQL客户端进行了测试：

mysql> set @j = '...'; -- your example JSON
mysql> select json_contains(@j, '{"Value":"10"}');
+-------------------------------------+
| json_contains(@j, '{"Value":"10"}') |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains(@j, '{"Value":10}');
+-----------------------------------+
| json_contains(@j, '{"Value":10}') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

所以请确保它是一个字符串：

return $builder->whereJsonContains('sizes',[['Value' => (string) $value]]);