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();
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)
how to move extracted value from char to integer