Laravel Filter JSON Data with SQL LIKE Operator

user_14334750 0
Viewed 206

Code

 //text to search   
     $details  = "Successfully";
        
        ActivityLog::with('getCauserDetails')
        ->when($details ?? false, function ($q) use ($details) {
            $q->whereJsonContains('properties->activity', $details);
        })
        ->get()
        ->toArray();

Table Structure

id          - int
name        - varchar
properties  - json
user_id     - int

Json Data

{
   "ip":"192.168.0.1",
   "platform":"Windows",
   "activity":"Successfully logout"
}

{
   "ip":"192.168.0.1",
   "device":"WebKit",
   "browser":"Chrome",
   "platform":"Windows",
   "activity":"Successfully logged in"
}

Question: Above code have been successfully to search the value of data inside the JSON data but need to search into full sentence. For example, "Successfully logout", if I search with "Successfully" sentence, it's will not filter the data. Does anyone know how to filter it's with the SQL LIKE Operator inside the JsonContains, mean that if I filter with sentence "Successfully", it's will also return the data instead of full sentence.

eloquent laravel json
1 Answers

I didn't find any appropriate Laravel method but your query in SQL would be like this. It seems you don't have other choice except using raw query:

SELECT * 
FROM table_name
WHERE JSON_EXTRACT(properties, "$.activity") LIKE '%Successfully logout%'
user_55263350
Related