DEV Community

Cover image for Getting a JSON element from a DB in Eloquent
Graham Morby
Graham Morby

Posted on

Getting a JSON element from a DB in Eloquent

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"}
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

And that will pull the data we need and all is great!

Enjoy folks and as always any updates or code reviews on this post please leave a comment down below

Top comments (1)

Collapse
 
sashaajin profile image
Sasha Ajintarev

how to move extracted value from char to integer