Getting a JSON element from a DB in Eloquent

#php #laravel #tutorial

So I had an issue come up today that required me to pull some data out of a DB for a report. That included some data that was stored in a JSON object.

So we have a column called params and in that we have a JSON Object that looks like so

{"1": "one", "2": "two", "3": "three", "4": "four"}

So in my report, I need to access number 3, and how we do that is by using json_extract(), like so

$results = DB::table('testing')
                   ->select(
                       DB::raw('json_extract(params, "$.4") as number'))->get();

So in the json_extract the first param it expects is the column name and following that is the field. You can then add more items to the select statement using a comma and we can write a full query like so

$results = DB::table('testing')
             ->select(
                'id',
                DB::raw('json_extract(params, "$.4") as number'),
                'type'
                'created_at'
)->get();