Working with MySQL JSON Columns in Laravel: Custom Properties Example

JSON fields are gaining more popularity since they became officially supported in MySQL 5.7.8. Even the popular Spatie Laravel Medialibrary package use them, so why shouldn't we? This tutorial will show you an example.

Let's say we have an e-shop project and want to store products. For some of them, we don't really know what the fields will be - some will require sizes, others will require colors, origin country, manufacturer etc. So here's where JSON field may become useful - we will store whatever custom properties data there.

Here's the form for our product:

Notice: For the simplicity, I didn't implement JavaScript add/remove dynamic fields, this is outside of JSON fields article. Here it's hard-coded to 5 fields.

Step 1. Back-end: Migration + Model

To create a JSON field, all we need to do in Laravel migration is use ->json() method:

Schema::create('products', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->decimal('price', 15, 2);
    $table->json('properties');
    $table->timestamps();
    $table->softDeletes();
});

Next, we need to tell our model app/Models/Product.php to cast that column from JSON to an array automatically:

class Product extends Model
{
    protected $casts = [
        'properties' => 'array'
    ];

This way, we will receive $product->properties as array and don't need to do json_decode() at all.

Step 2. Blade Form With Array

As I mentioned before, I added five fields for properties, user may fill one of them or all five:

<form action="{{ route("admin.products.store") }}" method="POST">
    @csrf
    <div class="form-group">
        <label for="name">Name</label>
        <input type="text" name="name" class="form-control">
    </div>
    <div class="form-group">
        <label for="price">Price*</label>
        <input type="number" name="price" class="form-control" step="0.01">
    </div>
    <div class="form-group">
        <label for="properties">Properties</label>
        <div class="row">
            <div class="col-md-2">
                Key:
            </div>
            <div class="col-md-4">
                Value:
            </div>
        </div>
        @for ($i=0; $i <= 4; $i++)
        <div class="row">
            <div class="col-md-2">
                <input type="text" name="properties[{{ $i }}][key]" class="form-control" value="{{ old('properties['.$i.'][key]') }}">
            </div>
            <div class="col-md-4">
                <input type="text" name="properties[{{ $i }}][value]" class="form-control" value="{{ old('properties['.$i.'][value]') }}">
            </div>
        </div>
        @endfor
    </div>
    <div>
        <input class=" btn btn-danger" type="submit">
    </div>
</form>

As you can see, a simple @for loop and every key-value pair has an index in the array, from 0 to 4.

Step 3. Saving Properties

Our ProductController::store() method will be simple.

public function store(StoreProductRequest $request)
{
    $product = Product::create($request->all());
    return redirect()->route('admin.products.index');
}

Yes, that's it. Some validation for required fields in StoreProductRequest, but nothing more fancy. And we don't need to do anything with our JSON field, because we're already passing the array from Blade, so it will be automatically casted to JSON.

This is how it will look in the database:

We have only one problem. In this example, I'm not validating for empty values so they will still be stored in JSON, like this - see last two values:

[
  {"key":"Size","value":"XL"},
  {"key":"Color","value":"Blue"},
  {"key":"Country","value":"China"},
  {"key":null,"value":null},
  {"key":null,"value":null}
]

To avoid that, we need to eliminate null values from the array. I will use Eloquent mutator feature and transform the array to the one without empty values in app/Product.php model:

public function setPropertiesAttribute($value)
{
    $properties = [];

    foreach ($value as $array_item) {
        if (!is_null($array_item['key'])) {
            $properties[] = $array_item;
        }
    }

    $this->attributes['properties'] = json_encode($properties);
}

Notice: I know there's probably more elegant way to perform this array operation, I just thought it's not that relevant to this article to search for one-liner array solution.

Step 4. Showing Properties

In the table of products, we will probably want to show something like this:

To achieve that, we will simply do a @foreach in the <td> cell of resources/views/products/index.blade.php file:

@foreach($products as $product)
    <tr>
        <td>
            {{ $product->name ?? '' }}
        </td>
        <td>
            {{ $product->price ?? '' }}
        </td>
        <td>
            @foreach ($product->properties as $property)
                <b>{{ $property['key'] }}</b>: {{ $property['value'] }}<br />
            @endforeach
        </td>

Step 5. Edit/Update Properties

Edit form will have the same structure, just the values of the fields will be set from the array. Here's the part of our resources/views/products/edit.blade.php:

<div class="form-group">
    <label for="properties">Properties</label>
    <div class="row">
        <div class="col-md-2">
            Key:
        </div>
        <div class="col-md-4">
            Value:
        </div>
    </div>
    @for ($i=0; $i <= 4; $i++)
        <div class="row">
            <div class="col-md-2">
                <input type="text" name="properties[{{ $i }}][key]" class="form-control"
                  value="{{ $product->properties[$i]['key'] ?? '' }}">
            </div>
            <div class="col-md-4">
                <input type="text" name="properties[{{ $i }}][value]" class="form-control"
                  value="{{ $product->properties[$i]['value'] ?? '' }}">
            </div>
        </div>
    @endfor
</div>

With controller's update() method, it's really similar to store() - we just use all request to update the data.

public function update(UpdateProductRequest $request, Product $product)
{
    $product->update($request->all());
    return redirect()->route('admin.products.index');
}

That's it, JSONs are simple, right?