My table has a json encoded column called skills and I have to check if any value exist in another array $skillArray in a eloquent query. How can I achieve this?

Employee::whereIn('skills', $skillArray);

skills has values like "["1","2","15"]" (string) (in DB)
$skillArray is [9,4] (array)
Attribute casting not preferred.

CodePudding user response：

If you don't want to use JSON column / JSON column queries or a many-to-many relationship table, you would have to use a regular expression WHERE clause:

$regex = '"(' . implode('|', $skillArray) . ')"';
Employee::where('skills', 'REGEXP', $regex);

This will assume:

• The skills column is a string of a JSON array with IDs
• $skillArray is always an array with IDs
• Left and right of the number is a ".
• Also: Numbers in $skillArray may be in any order