Laravel SQL搜索JSON类型列 [英] Laravel sql search json type column

问题描述

mysql具有表'subscribe'表，如下所示:

The mysql has table 'subscribe' table and it's as follows:

column   type
id        int
condition  json
type_id    id

，示例如下:

"id": "1",
"condition": "{\"id\":\"2\",\"class\":\"master\",\"zone\":\"west\",\"price\":\"511\"}",
"type_id": "1"

，我想选择与列条件匹配的列，例如"zone" ="west" laravel 5.2支持该命令

and I want to select the column which match in the column condition like "zone"="west" the laravel 5.2 has support the order

 $subscribe = DB::table('subscribe')->where('condition->class', 'master')->get();

错误

 Column not found: 1054 Unknown column 'condition->class' in 'where clause'(
SQL: select * from `subscribe` where `condition->class` = master)

我需要的是获得与conditon-> class = master的条件相匹配的术语.我需要在模型中选择与需求相匹配的数据. 我不知道怎么了任何帮助将不胜感激.

I need is get the term which match the condition where conditon->class = master .I need to select the data which match the need in the model. I don't know what's wrong. Any help would be much appreciated.

推荐答案

我相信正确的语法是:

 $subscribe = DB::table('subscribe')->where('condition->"$.class"', 'master')->get();

在 https:上查看本段下面的示例: //dev.mysql.com/doc/refman/5.7/zh-CN/json-search-functions.html

• 列->路径

• column->path

在MySQL 5.7.9及更高版本中，->运算符与两个参数一起使用时用作JSON_EXTRACT()函数的别名，两个参数在左侧是列标识符，在右侧是根据JSON求值的JSON路径文档(列值).您可以使用此类表达式代替SQL语句中出现的列标识符.

In MySQL 5.7.9 and later, the -> operator serves as an alias for the JSON_EXTRACT() function when used with two arguments, a column identifier on the left and a JSON path on the right that is evaluated against the JSON document (the column value). You can use such expressions in place of column identifiers wherever they occur in SQL statements.

这篇关于Laravel SQL搜索JSON类型列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！