Laravel's MySQL JSON Manager

So, do you have a JSON data type column in your MySQL database and you're using Laravel framework? Well, this tutorial might help you manage your JSON column in more easy way.

Although Laravel allows you to cast JSON to array in order for you to manipulate the data easily:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
    /**
     * The attributes that should be cast to native types.
     *
     * @var array
     */
    protected $casts = [
        'options' => 'array',
    ];
}

$user = App\User::find(1);

$options = $user->options;

$options['key'] = 'value';

$user->options = $options;

$user->save();

Would it be nice if we have an API that can simplify the process of adding, updating and deleting data in the JSON column like so?

$user = App\User::find(1);

$user->options()->add('key', 'value');
$user->options()->add(['key1' => 'value1', 'key2' => 'value2']);
$user->options()->update('key', 'value');
$user->options()->update(['key1' => 'value1', 'key2' => 'value2']);
$user->options()->delete('key');
$user->options()->delete(['key1', 'key2']);
$user->options()->get('key');
$user->options()->key;
$user->options()->all();

Let's try it out. First let's create a unit test for it:

php artisan make:test MySQLJSONManagerTest --unit

class MySQLJSONManagerTest extends TestCase
{
    use DatabaseMigrations;

    /** @test */
    public function can_add_an_item_to_the_json_column()
    {
        $user = factory('App\User')->create();

        $user->options()->add('key1', '123456');

        $this->assertDatabaseHas('users', ['options' => '{"key1":"123456"}']);
    }

    /** @test */
    public function the_add_method_can_work_with_array()
    {
        $user = factory('App\User')->create();

        $user->options()->add(['key1' => '123456', 'key2' => '654321']);

        $this->assertDatabaseHas('users', ['options' => '{"key1":"123456","key2":"654321"}']);
    }

    /** @test */
    public function can_update_an_item_in_the_json_column()
    {
        $user = factory('App\User')->create();

        $user->options()->add(['key1' => '123456', 'key2' => '654321']);
        $user->options()->update('key1', 'updated');

        $this->assertDatabaseHas('users', ['options' => '{"key1":"updated","key2":"654321"}']);
    }

    /** @test */
    public function the_update_method_can_work_with_array()
    {
        $user = factory('App\User')->create();

        $user->options()->add(['key1' => '123456', 'key2' => '654321']);
        $user->options()->update(['key1' => 'updated', 'key2' => 'updated']);

        $this->assertDatabaseHas('users', ['options' => '{"key1":"updated","key2":"updated"}']);
    }

    /** @test */
    public function can_delete_an_item_in_the_json_column()
    {
        $user = factory('App\User')->create();

        $user->options()->add(['key1' => '123456', 'key2' => '654321']);
        $user->options()->delete('key1');

        $this->assertDatabaseHas('users', ['options' => '{"key2":"654321"}']);
    }

    /** @test */
    public function the_delete_method_can_work_with_array()
    {
        $user = factory('App\User')->create();

        $user->options()->add(['key1' => '123456', 'key2' => '654321']);
        $user->options()->delete(['key1', 'key2']);

        $this->assertDatabaseHas('users', ['options' => '[]']);
    }

    /** @test */
    public function can_get_specific_value_by_key()
    {
        $user = factory('App\User')->create();
        $key1 = '123456';

        $user->options()->add(['key1' => $key1, 'key2' => '654321']);        

        $this->assertEquals($key1, $user->options()->get('key1'));
    }

    /** @test */
    public function can_use_dynamic_property()
    {
        $user = factory('App\User')->create();
        $key1 = '123456';

        $user->options()->add(['key1' => $key1, 'key2' => '654321']);        

        $this->assertEquals($key1, $user->options()->key1);
    }

    /** @test */
    public function can_get_all_data_using_all_method()
    {
        $user = factory('App\User')->create();
        $options = ['key1' => '123456', 'key2' => '654321'];

        $user->options()->add($options);        

        $this->assertEquals($options, $user->options()->all());
    }
}

phpunit tests/Unit/MySQLJSONManagerTest
There were 2 errors:

1) Tests\Unit\MySQLJsonManagerTest::can_add_an_item_to_the_json_column
BadMethodCallException: Call to undefined method Illuminate\Database\Query\Builder::options()

Next, let's make sure we have the options column in our users table and must be fillable and cast to array:

$table->json('options')->nullable();

protected $fillable = ['options'];

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

Next, let's create a trait called app/Traits/MySQLJSONColumnManager.php:

<?php

namespace App\Traits;

use App\Managers\MySQLJSONColumnManager as BaseManager;

trait MySQLJSONColumnManager
{
    public function __call($method, $arguments)
    {
        if (property_exists($this, 'casts')) {
            if (array_key_exists($method, $this->casts) && in_array($this->casts[$method], ['array', 'json'])) {
                return new BaseManager($this, $method);
            }
        }

        return parent::__call($method, $arguments);
    }
}

What we're doing here is we use the PHP __call magic method. What it does is it creates a method dynamically if a method does not exist in the class, which exactly what we want in this case, it is also known as method overloading in PHP. Then we check if the given model has a casts property and the method name is one of the key in the casts array and check if it's cast to array or json, then we return a new instance of App\Managers\MySQLJSONColumnManager class and passing the model instance and the method name in the constructor. Otherwise, let's just return the parent __call magic method.

Next, let's use the trait in the model that has a JSON column in it. In this case let's use it in the User model:

use App\Traits\MySQLJSONColumnManager;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use Notifiable, MySQLJSONColumnManager;
}

Next, let's create the App\Managers\MySQLJSONColumnManager class:

<?php

namespace App\Managers;

use Illuminate\Database\Eloquent\Model;

class MySQLJSONColumnManager
{
    /**
     *  The model instance
     * 
     *  @var mixed
     */
    protected $model;

    /**
     *  The list of data
     * 
     *  @var array
     */
    protected $data;

    /**
     *  The the model attribute name.
     * 
     *  @var string
     */
    protected $attribute;

    /**
     *  Create a new data instance
     * 
     *  @param \Illuminate\Database\Eloquent\Model $model
     *  @param  string $attribute
     */
    public function __construct(Model $model, string $attribute)
    {
        $this->model = $model;        
        $this->attribute = $attribute;

        $this->data = $model->$attribute ?: [];               
    }
}

Next, let's create the add method:

/**
 *  Add a new item in the json column.
 * 
 *  @param array|string $key
 *  @param string $value
 */
public function add($key, $value = null)
{
    if (is_array($key)) {
        foreach ($key as $key => $value) {
            $this->data[$key] = $value;
        }
    } else {
        $this->data[$key] = $value;
    }

    $this->persist();
}

Next, let's create the persist method:

/**
 *  Persist the data
 * 
 *  @return mixed
 */
protected function persist()
{
    return $this->model->update([$this->attribute => $this->data]);
}

Next, let's create the update method:

/**
 *  Update an item in the json column.
 * 
 *  @param  array|string $key
 *  @param  string $value
 *  @return mixed
 */
public function update($key, $value = null)
{
    if (! is_array($key)) {
        $key = [$key => $value];
    }

    $this->data = array_merge(
        $this->data,
        array_only($key, array_keys($this->data))
    );

    $this->persist();
}

Next, let's create the delete method:

/**
 *  Remove item from the data.
 * 
 *  @param  array|string $key
 *  @return mixed
 */
public function delete($key)
{
    $exist = false;

    if (is_array($key)) {            
        foreach ($key as $value) {
            if (isset($this->data[$value])) {
                unset($this->data[$value]);
                $exist = true;
            }
        }
    }

    if (is_string($key) && isset($this->data[$key])) {
        unset($this->data[$key]);
        $exist = true;
    }

    if ($exist) {
        return $this->persist(); 
    }
}

Next, let's create the get method:

/**
 *  Retrieve a given setting
 * 
 *  @param  string $key
 *  @return string
 */
public function get($key)
{
    if ($this->has($key)) {
        return array_get($this->data, $key);
    }
}

Next, let's create the has method:

/**
 *  Determine if the given setting exists.
 * 
 *  @param  string  $key
 *  @return boolean
 */
protected function has($key)
{
    return array_key_exists($key, $this->data);
}

Next, let's add a __get magic method for the ability to access dynamic properties:

/**
 *  Magic property access for data
 * 
 *  @param  string $key
 *  @return string
 */
public function __get($key)
{
    return $this->get($key);              
}

Lastly, let's create the all method:

/**
 *  Retrieve an array of all data
 * 
 *  @return array
 */
public function all()
{
    return $this->data;
}

That's all there is to it. We have now the API that we can use to manipulate a JSON column in our MySQL database table. As always, if you have any questions please write it down in the comment below. See you in the next lesson. Thanks!

View the source code for this lesson on GitHub.