Search in JSON column does not work with Query Builder and Eloquent Model (but works with raw query)

Hi, in Laravel 5.3, I'm trying to use that short syntax for the JSON columns:

$allLeadsEloquent = LeadsCache::where('json->linked_company_id', $companyId)
    ->get();

The result: empty collection. Note: the same result is with Query Builder (DB('table_name')).

Now. I open Debugbar and grab the "Raw query" produced by this code and run this query in the Sequel Pro: there it finds some rows!

Here's the how the raw query looks:

select * from `leads_cache` where `json`->'$."linked_company_id"' = '68743242'

What possibly could go wrong with this?…

pilat

8 years ago

Best Answer

Level 41

I've found the issue. Hope, it may help someone:

It turns out that linked_company_id is stored as string in the json structure I search in. Like: …,"linked_company_id":"68743242",…n't ask me why, I get this data from external service's API). So, I've tried the following code:

$allLeadsEloquent = LeadsCache::where('json->linked_company_id', (string)$companyId)
    ->get();

And it worked.

Still, I find it not very neat, for at least the following reasons:

How come the Debugbar still produces the query that actually finds rows?
This "strict type match" is quite hard to debug. Especially, considering that type match is not required when searching in normal (not JSON) column.

Please or to participate in this conversation.