JSON string in MySQL to json response

Question

I have a JSON formatted string stored in a column (meta_data) in a mysql database, stored in the table it looks something like this for example:

 {"Format":"JPEG","Geometry":"3216x2136","size":{"width":3216,"height":2136}}

Now if I use the following:

 $meta_data = DB::query->get();
 return $meta_data;

I get:

 [
   {
     "meta_data": "{\"Format\":\"JPEG\",\"Geometry\":\"3216x2136\",\"size\":{\"width\":3216,\"height\":2136}
   }
 ]

I also get the same result if I use:

 $meta_data = json_decode(DB::query->get());
 return $meta_data;

Similarly, using response()->json($meta_data); returns it as a string.

It seems to me that it needs to go the next step down but I haven't been able to get anything close to what I'm after, which is ideally:

 [
   {
     "meta_data":
     { 
      "Format":"JPEG",
      "Geometry":"3216x2136",
      "size":
      {
        "width":3216,
        "height":2136
     }
   }
  }
 ]

Answer 1

DB::query->get() will return an array of stdClass objects (assuming query is just shorthand for your query conditions). You will need to loop through the array and convert the meta_data field of each entry to a json object manually.

$records = DB::query->get();

foreach ($records as $record) {
    $record->meta_data = json_decode($record->meta_data);
}

return $records;

Another option would be to create a Model for the table, and then add the meta_data field to the $casts property to automatically cast it to json.

Model:

class Attachment extends Model
{
    protected $casts = [
        'meta_data' => 'json',
    ];
}

Controller:

// assume "query" is shorthand for your query conditions
$records = Attachment::query->get();

return $records;