How to remove quotes and backslashes from returned JSON in Laravel API

Racking my brains with this one.

I have a few datasets, a couple of which are JSON encoded one-dimensional PHP arrays pushed to a database column as type 'json'.

E.g. in my migration: $table->json('organisations_list')->nullable();

The column added to the DB looks like this: [ "US show", "UK show" ]

In my index method I'm grabbing everything from this table and returning the data:

$data = DB::table('data') ->select('data.*') ->orderBy('name') ->get(); return $data;

But when I view it as JSON, the array cols look like this:

"organisations_list": "[\"US show\", \"UK show\"]",

So when I go to consume this elsewhere (Javascript), it is of type string and not array as I want/need.

What do I need to do to achieve the following:

a) get rid of the quotes around the array so it is not a string

b) get rid of the backslashes

Any advice appreciated!

tykus

3 years ago

Level 104

Why not use an Eloquent model and cast the column as json?

https://laravel.com/docs/9.x/eloquent-mutators#array-and-json-casting

Otherwise, if you must use the Query Builder, you will need to map/transform the Collection and json_decode the relevant column(s):

$data = DB::table('data')
    ->select('data.*') 
    ->orderBy('name') 
    ->get()
    ->transform(function ($obj) {
        return tap($obj, fn ($o) => $o->column_name = json_decode($o->column_name));
    });

return $data;

The transform operation maps over the objects in the Collection changing the column_name property for a json-decoded version of same.

zenith707

3 years ago

Level 1

@tykus Would you mind clarifying point 1 on json-casting?

In my model I have:

protected $fillable = ['organisations_list']; protected $casts = ['organisations_list' => 'array']

and on my database update:

$data->update(['organisations_list' => $ol]);

Pushed data again but nothing has changed. What am I missing?

tykus

3 years ago

Level 104

@zenith707 And are you fetching the data using Eloquent, so that the cast works?

$data = Model::orderBy('name')->get();

Please or to participate in this conversation.