How can I resolve error : Call to undefined function App\Http\Controllers\JSON_EXTRACT() in laravel 5.3?

Question

For example, I have a column named json in table A

Json column contains json data like this :

record 1 : {"dept_code": "012", "unit_code": "22"}
record 2 : {"dept_code": "013", "unit_code": "23"}
etc

I want to take the data records with json column that contain dept_code = 012

I try like this :

$id = "012";
$data = \DB::table('table_A')
           ->select('*')
           ->where(JSON_EXTRACT('json', "$.dept_code"), '=', '"'.$id.'"')
           ->get();

There exist error like this :

Call to undefined function App\Http\Controllers\JSON_EXTRACT()

How can I resolve that?

Answer 1

You can use laravel's JSON where clauses. E.g.

$id = "012";
$data = DB::table('table_A')
           ->where('json->dept_code', $id)
           ->get();

This works in Postgres and MySQL(since 5.7.8)

Query Builder Where Clauses