Laravel query MySQL JSON column using equal operator

Question

I'm trying to query a MySQL database table which contains a JSON column.

In MySQL the following query runs

mysql> select members from `conversations` where `members` = CAST('[1,2,3]' AS JSON) limit 3;

And I'm able to see the result from mysql console.

+-----------+
| members   |
+-----------+
| [1, 2, 3] |
| [1, 2, 3] |
| [1, 2, 3] |
+-----------+

However, when I use a Laravel QueryBuilder to build this query. It throws grammar error.

The Laravel code (inside a controller):

$memberList = $request->input('members');
DB::table("conversations")->where('members', '=', DB::raw('CAST(\''.$memberList.'\'AS JSON'))->get();

The error message:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 (SQL: select * from `conversations` where `members` = CAST('[1,2,3]'AS JSON)

Could you please help me know where am I doing wrong?

Can you do this, dd($memberList) to check the value of $memberList — geckob
– geckob, Commented Mar 26, 2016 at 22:21
@geckob well, I think you can see from the error output. It is just[1,2,3] — xuanyue
– xuanyue, Commented Mar 26, 2016 at 22:25
@aldrin27 yes, I put in {'members':[1,2,3]}; in the get request. — xuanyue
– xuanyue, Commented Mar 26, 2016 at 22:34

Yilmazerhakan · Accepted Answer · 2016-06-30 20:11:07Z

3

Firstly you must convert $memberlist to string.

$memberList = $request->input('members');
$memberListString = '[' . implode(",", $memberlist) . ']';

Then you can use;

DB::table("conversations")
    ->whereRaw("members = CAST('".$memberListString."' AS JSON)")->get();

I'm sure it will work.

answered Jun 30, 2016 at 20:11

Yilmazerhakan

1,8751 gold badge14 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Laravel query MySQL JSON column using equal operator

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related