how to find the position of string search in a json array column in laravel

i have a query which returns boolean value from a json array column about whether the string i am searching exists or not

the column is something liek this —— [“a”,”b”,”c”] and the query is below

DB::table('product_lines as l')->where('l.id',$position->product_line_id)
 ->whereRaw('json_contains(l.types, '["b"]')')->first()->count();

but i want to get the position where the string was found… lets say for this it should return 1.

please help.

Answer

$pl = DB::table('product_lines as pl')
                     ->select(DB::raw('JSON_SEARCH(pl.types, "one", "b") as 
                           idx'))
                     ->where('id', $position->product_line_id)
                     ->whereRaw('JSON_CONTAINS(pl.types, '"b"', '$')')
                     ->first();
dd($pl->idx);

It will give you the output as "$[1]" "" are part of the output string. So you need to get the number between [] to get the index of the item in the array.

OR

$pl = DB::table('product_lines as pl')
                     ->where('id', $position->product_line_id)
                     ->whereRaw('JSON_CONTAINS(pl.types, '"b"', '$')')
                     ->first();

$arr = json_decode($pl->types);

dd(array_search("b", $arr));