Best way to store and load JSON from database in Laravel

Question

I'm trying to store json into a db and load it back

I tried to store

{name: "John", age: 31, city: "New York"}

It stored correctly. I checked the db, it showed correctly.

{name: "John", age: 31, city: "New York"}

I kept getting on the view

"{name: \"John\", age: 31, city: \"New York\"}"

This is my code.

public function store()
{

    $paste             = new Paste;
    $paste->uuid       = Str::uuid()->toString();
    $paste->data       = trim(Request::get('data',''));
    $paste->save();

    return Redirect::to('/paste/'.$paste->uuid)->with('success', 'Created');

}

public function show($uuid)
{
    $paste  = Paste::where('uuid',$uuid)->first();
    return response()->json($paste->data);
}

Any hints for me ?

Reproducible here

https://www.bunlongheng.com/paste

---

Try # 2

If I did this

public function show($uuid)
{


    $paste  = Paste::where('uuid',$uuid)->first();
    return View::make('layouts.fe.pastes.show', get_defined_vars());

}

and in my view, I only have this 1 line

{!!$paste->data!!}

I get the same data as what I submitted now.

{name: "John", age: 31, city: "New York"}

BUT the browser detected it as text, not a response JSON which defeated the purpose of what I am trying to do.

---

Try # 3

public function show($uuid)
{
    $paste  = Paste::where('uuid',$uuid)->first();
    return response()->json(stripslashes($paste->data));
    
}

result

"{name: \"John\", age: 31, city: \"New York\"}"

---

Try # 4

public function show($uuid)
{
    $paste  = Paste::where('uuid',$uuid)->first();
    return View::make('layouts.fe.pastes.show', get_defined_vars());
}

view

{{ json_encode($paste->data, JSON_UNESCAPED_SLASHES) }}

result

"{name: \"John\", age: 31, city: \"New York\"}"

---

Try #5

I think the issue is lying on the storing ... not the loading and rendering.

I tried

return response()->json($paste);

My JSON parser detected it ...

{
"id": 11,
"status": 0,
"uuid": "0c40f97d-7d98-42c6-864e-71d3ed81eed3",
"name": "n6ou",
"password": "",
"expiration": "",
"type": "json",
"data": "{name: \"John\", age: 31, city: \"New York\"}",
"created_at": "2021-04-22T22:53:11.000000Z",
"updated_at": "2021-04-22T22:53:11.000000Z"
}

This is what I used to store

$paste->data       = trim(Request::get('data',''));
$paste->save();

---

Try #6

For those of you that doubt my data/content

I've tried pasting the same line in Pastebin

It's cleaned, you can see below.

https://pastebin.com/raw/r9akUK1v

Answer 1

Database

In your database migrations add:

$table->json('data'); // Recommended. Supported in MySQL since version 5.7.8

or

$table->text('data');

The JSON column type is recommended as it allows you to do SQL queries on JSON data. See MySQL JSON Data Type

Model: Casting the Attribute

The next issue is that you need to be able to cast your data into a PHP array.

This is done by modifying the casts attribute in the model:

class Paste extends Model {
    protected $casts = [
        'data' => 'array'
    ];
}

See Array and JSON Casting for more information.

Now you can save data onto the attribute as a PHP array, and also assign it a PHP array.

 $paste = Paste::first();
 dump($paste); // Returns a PHP array     

 $paste->data = ['some-data' => 20, 'score' => 500];
 $paste->save();

Internally, when it saves the data, it automatically would convert it into a JSON string and save it in the database in the correct format.

Store Method

When taking in input as JSON, it highly depends in how you want to pass the data,

1. Sending form data with JSON content type (recommended)

My recommendation is to send the entire data as JSON in the POST body like so:

Content-Type: application/json
Body:
{
   "data": {
      "name": "John",
      "age": 31,
      "city": "New York"
   },
   "someOtherField": "Hello!"
}

Your store() method should now be (I've also added validation code):

public function store()
{
    $this->validate($request, [
        'data' => ['required', 'array'],
        'data.*.name' => ['required', 'string'],
        'data.*.age' => ['required', 'int'],
        'data.*.city' => ['required', 'string'],
    ]);
    
    $paste = new Paste();
    $paste->uuid = Str::uuid()->toString();
    $paste->data = $request->post('data'); // No need to decode as it's already an array
    $paste->save();

    return Redirect::to("/paste/{$paste->uuid}")
        ->with('success', 'Created');
}

2. Sending form data with form params

If however you insist in sending data through query params or form params, note these can only send strings. Therefore you need to send an encoded version of the JSON string to persists data types, as follows:

Form Params:
- data: '{"name": "John", "age": 31, "city": "New York"}'
- someOtherField: "Hello!"

The store method will now look like this:

    $this->validate($request, [
        'data' => ['required', 'json'], // I'm unsure if data is required
    ]);
    
    $data = json_decode($request->post('data'), true, JSON_THROW_ON_ERROR); // Needs to be decoded
    
    // validate $data is correct
    Validator::make($data, [
        'name' => ['required', 'string'],
        'age' => ['required', 'int'],
        'city' => ['required', 'string'],
    ])->validate();
    
    
    $paste = new Paste();
    $paste->uuid = Str::uuid()->toString();
    $paste->data = $data;
    $paste->save();

    return Redirect::to("/paste/{$paste->uuid}")
        ->with('success', 'Created');

Show Method

Your show method needs no changes:

public function show($uuid)
{
    $paste = Paste::where('uuid', $uuid)->first();
    return response()->json($paste->data);
}

Answer 2

1- Your column need to be of type json type

$table->json('data');

2- in your Model you need to cast your column to an array

  protected $casts = ['data' => 'array'];

3- sending data value to your controller must be an array so you can use array Laravel validation on it:

[
  'data' => 'required|array',
  'data.*.name' => 'required'
   ....
]

4- when you store your data it will be parsed automatically and the same when you retrieve your data column it will be converted to an array

Answer 3

1. Using ->json() as the migration method to store JSON data (https://laravel.com/docs/8.x/migrations#column-method-json)
2. Refer to "Array & JSON Casting" (https://laravel.com/docs/8.x/eloquent-mutators#array-and-json-casting) for how do you prepare the data

I know the answer is not in paragraphs as others, but I like to make it simple and straight. Is it the best solution? No one can tell you that nor prove that? Is this method going to work, no one can tell you that nor prove that, but at least it boosts up your success rate. Let me know if there is anything else I could help with! Good Luck

Answer 4

If you want to store data as json on DB and restore it just do the following (I always use this way):

1- Add your data to array:

$data["name"] = "John";
$data["age"] = 31;
$data["city"] = "New York";

2- Encode the array and add it to the database (you can store it as text)

$encodedData = json_encode($data);

3- If you want to add nested json data just make your array nested array.

4- When you restore the data just use json_decode to decode it

Answer 5

Just add this to your Model.

protected $casts = [
    'data' => 'object'
];

Then you can get in your view like this:

{{ $data->data->name }}