Working with MySQL JSON Columns in Laravel: Custom Properties Example – Laravel Daily

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:

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

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:

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.

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:

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:

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:

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:

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

That’s it, JSONs are simple, right?