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/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?

Like our articles?
Check out our Laravel online courses!

14 COMMENTS

  1. well, that’s nice indeed, but how does that differ from storing JSON in a text field?
    this example doesn’t cover that in any way and I feel like it really doesn’t make any difference for Laravel as we’re still pulling the whole string from db

    • Thanks for the comment, Zakius. Well, it’s a NATIVE field type from MySQL so it’s more optimized for that specific storage, in text field with simple string you would have to parse/encode/decode everything manually.

      • I guess casts property works thanks to using json field, without it we’d have to use actual mutator probably, but in the end it wouldn’t be much different

        idk what are the exact performance improvements on the db side, I know there are functions allowing modifying data inside JSON, though does Eloquent make use of them or just updates whole field anyway?

  2. What about large scale projects? You have big online shop and saving data in json when you would like to get data will be differents the speed slow down?!

    • When you like to get data, speed won’t slow down. Speed will slow down if you are going to SEARCH for that data in json fields, like filtering by those properties. They are not built effectively for search, for these cases then I would recommend Algolia or ElasticSearch.

  3. Spatie also has a package for working with JSON columns. It’s really sweet: https://github.com/spatie/laravel-schemaless-attributes

    But there’s one gotcha when working with JSON fields and Sequel Pro that you should be aware of…

    In my experience (with the latest version of Sequel Pro) when you export your tables and then try to reimport them using Sequel Pro, it always fails. Here’s more info https://github.com/sequelpro/sequelpro/issues/2647

    However, the mysqldump command works fine.

LEAVE A REPLY

Please enter your comment!
Please enter your name here