Using JSON in SQL table with Laravel

JSON is great, it’s flexible, easy to use and compatible with pretty much everything. The problem is when storing it in a SQL database, you’ll find yourself encoding and decoding JSON all over the place, and things get out of hand pretty fast. To solve this problem I created a trait a while back that takes care of all the dirty work for me, and I made it flexible enough to be reusable with all the projects that I needed it in.

To demonstrate this trait I’m going to use a general settings table as an example, this table is key value pairs with the value column holding the JSON string, the migration looks something like:

Schema::create('settings', function (Blueprint $table) {
    $table->increments('id');
    $table->string('key', 50);
    $table->json('value');
    $table->timestamps();
});

For example, assume we have the following JSON string in the settings table:

{
    "text":"Lorum epsom",
    "image":"public\/uploads\/EeoWjkoNKmnbszaO5I.jpeg"
}

To get the value of text in conventional way we would have to do something like this:

$encodedJson = json_decode($setting->value);
return $encodedJson->text;

and to set it it would be something like:

$decodedJsonArray = json_decode($setting->value, true);
$decodedJsonArray['text'] = 'New text';
$setting->value = json_encode($decodedJsonArray);

It would be much simpler if I was able to get and set a property in the decoded JSON value without having to access the value property and then decoding the JSON in it every time. So the idea here is to access a property on a setting object that is not directly there. And this is exactly what PHP magic methods __get and __set do.

So this trait can be used by any Model in Laravel that has a JSON column. There two things this trait needs to know. First, the name of the column in the table that holds the JSON string, and second is all the names that are actual columns in the table and should not be assumed they are in the JSON decoded object, those properties are called $jsonColumn and $columns respectively in the trait. There are also the two magic methods __get and __set, so the trait is as follows:

<?php

namespace App;

trait Jasonable
{
    protected $jsonColumn = 'value';
    protected $columns = ['created_at', 'updated_at'];
    

    public function __get($name)
    {
        if (in_array($name, $this->columns)) 
            return $this->getAttribute($name);
        if (!isset($this->attributes[$this->jsonColumn]) || $this->attributes[$this->jsonColumn] === null) 
            return false;
        $encoded = json_decode($this->attributes[$this->jsonColumn]);
        return isset($encoded->$name) ? $encoded->$name : false;
    }

    public function __set($name, $value)
    {
        if (in_array($name, $this->columns)) 
            return;
        $decodedArray = json_decode($this->attributes[$this->jsonColumn], true);
        $decodedArray[$name] = $value;
        $this->attributes[$this->jsonColumn] = json_encode($decodedArray);
    }
}

So assuming that the Setting model uses this trait, working with the model objects would be as simple as:

$setting->text = 'New text';
return $setting->text;

So making use of PHP magic methods made working with JSON in a Laravel model much easier. Hope someone would find this useful.