DEV Community

loading...
Cover image for Getting a JSON element from a DB in Eloquent

Getting a JSON element from a DB in Eloquent

37shadesofgrey profile image Graham Morby | VueJs & Laravel Developer ・1 min read

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

Discussion (0)

pic
Editor guide