Laravel sql search json type column

Question

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

column   type
id        int
condition  json
type_id    id

and the example as follows:

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

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();

Error

 Column not found: 1054 Unknown column 'condition->class' in 'where clause'(
SQL: select * from `subscribe` where `condition->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.

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

I believe the correct syntax is:

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

See the example below this paragraph, on https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html

column->path

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 1, 2016 at 0:37

act28

1943 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Murlidhar Fichadia · Accepted Answer · 2016-04-30 17:50:24Z

0

The thing is the where clause in query is considering as a column name in condition->class and not as a laravel code. you want to extract the value from json. but the query doesn't understand that.

What you need to do is pass entire table as it is in json format to the view and then extract in the view.

I suggest do something like this : Here, $subscribe has entire table in json . which you can access it by :

$subscribe = DB::table('subscribe')->all();

return response()->json(array('subscribe' => $subscribe));

Then in the view do:

@if($subscribe->condition->class == 'master')

id      : {{ $subscribe->id }}
type id : {{ $subscribe->type_id }}

@endif

Updated Code

//get condition in json
$subscribe = DB::table('subscribe')->select('condition')->get();

then, 

// convert json to array
$subscribe_array = json_decode($subscribe, true);
//  create a new collection instance from the array
$collection_array = collect($subscribe_array);


if($collection_array['class'] == 'master')
{

//do something

}

Something like this do the trick

edited Apr 30, 2016 at 17:50

answered Apr 30, 2016 at 17:18

Murlidhar Fichadia

2,6298 gold badges47 silver badges104 bronze badges

5 Comments

ray Over a year ago

Thank you, but I don't want to sent it to the view then get it, which I need is get the term which match the condition where conditon->class = master .I need to select the data in the model .

Murlidhar Fichadia Over a year ago

okay. condition is the name of the column and not the json. can you try this class = 'master' instead of condition->class = 'master'

ray Over a year ago

MySQL 5.7.8 added support for a native JSON data type. Laravel 5.2 now adds support for this column type.

Murlidhar Fichadia Over a year ago

also if you want it as condition->class. you will have to append json like this: { "condition": { ..your data... } } and what you have is { ...your data... }

Murlidhar Fichadia Over a year ago

Ok, I have an idea convert json toArray() method which then can be compared using php code in controller itself. I will try to writing the code

Collectives™ on Stack Overflow

Laravel sql search json type column

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related