xafa's avatar
Level 1

Getting items using where on json column resulting in empty data

As the title says, i'm trying to search by json column, but i get empty collection. You can see that in result, my sql query is correct, but somehow it gives me no data, while same query on my database works as expected. I'm doing something wrong here?

I'm using Lumen 5.7.1 and Mysql 5.7.24

Code:

$logs = Activity::where('properties->id', 4);
$sql = str_replace_array('?', $logs->getBindings(), $logs->toSql());
dd($sql, $logs->get());

Result:

"select * from `activity_log` where `properties`->'$."id"' = 4"
Collection {#133
  #items: []
}
0 likes
1 reply
xafa's avatar
xafa
OP
Best Answer
Level 1

Ok i solved it. The problem was in format of the json in table. As a id parameter i give integer instead of string. In my table it looks like this, so thats why i get empty data.

{"id":6}        // incorrect
{"id":"6"}      // correct!

Please or to participate in this conversation.