JSON Column Type: in Which Scenarios?

Now, let's discuss JSON column type in the database. Because it is fully supported in Laravel, you can create a migration file using the JSON column type. And why or why not you should use that?

Typically, the JSON field is used for a flexible structure, with a high probability of that structure being changed. In other words, you need a field for some properties, but you don't precisely know what properties will be there in the future. To avoid creating a new database table or changing the structure in the future, you just save properties as a flexible JSON.

Let's take a look at examples.

Example 1: Spatie Medialibrary

The first example is from a well-known spatie/laravel-medialibrary package.

Part of this package to upload the images is conversions. A conversion is any variant of the image, thumbnail, crop thumbnail, etc.

You define conversions with width, height, and other parameters, and it saves a separate file performing all the conversions.

use Illuminate\Database\Eloquent\Model;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
use Spatie\MediaLibrary\HasMedia;
use Spatie\MediaLibrary\InteractsWithMedia;
 
class YourModel extends Model implements HasMedia
{
    use InteractsWithMedia;
 
    public function registerMediaConversions(?Media $media = null): void
    {
        $this->addMediaConversion('thumb') 
              ->width(368)
              ->height(232)
              ->sharpen(10);
    }
}

But in the database, in the media table for that package, we have a column generated_conversions where the conversion name and whether it's generated are saved.

Conversions can have many names, and each Model that can have some media can have conversions with different names. So, this case is perfect for the JSON column type.

The conversions are defined in the Model.

use Spatie\Image\Enums\Fit;
use Spatie\MediaLibrary\MediaCollections\Models\Media;
 
class User extends Authenticatable implements HasMedia
{
    // ...
 
    public function registerMediaConversions(Media $media = null): void
    {
        $this
            ->addMediaConversion('thumb')
            ->fit(Fit::Crop, 50, 50)
            ->nonQueued();
 
        $this
            ->addMediaConversion('preview')
            ->fit(Fit::Crop, 120, 120)
            ->nonQueued();
    }
}

In the database, we have conversion names in a JSON format.

Now, it has its disadvantages. First, it's not a relational database. So, there are no relationships, and it's hard to ensure 100% that the data is correct. You don't easily see what's inside.

Next, there could be performance issues. You can perform operations like finding where JSON is and searching inside that JSON, but of course, it is slower.

Example 2: Personal Experiment

As an experiment, I made a simple project with a list of 10000 products.

The database has a JSON column called properties, listing size, color, and origin.

database/migrations/xxx_create_products_table.php:

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->text('description');
    $table->integer('price');
    $table->json('properties');
    $table->timestamps();
});

Properties are cast to an array in the Model.

app/Models/Product.php:

class Product extends Model
{
    // ...
 
    protected function casts(): array
    {
        return [
            'properties' => 'array',
        ];
    }
}

And are shown in the View.

// ...
@foreach($products as $product)
    <div class="mb-4 border-b-2 pb-4">
        <div class="font-bold">{{ $product->name }}</div>
        <p class="text-sm mb-2"> {{ $product->description }}</p>
        <div class="text-xl">${{ number_format($product->price / 100, 2) }}</div>
        <div class="mt-2 text-sm">
            @foreach($product->properties as $key => $value)
                <div>
                    <span class="font-bold">{{ $key }}</span>: {{ $value }}
                </div>
            @endforeach
        </div>
    </div>
@endforeach
// ...

This is all fine if you get the product and then parse the properties, showing them one by one or showing specific properties. But what if you want to search by property?

For example, look for products that originate in China. In the where clause, you would use the -> to query the JSON column.

$products = Product::where('properties->origin', 'China')->get();

Check JSON Where Clauses in the Laravel documentation.

The query takes almost 6ms to run.

But, if you had a relation to the origins table with origin_id on the products table, you could search by the origins ID.

$products = Product::where('origin_id', 1)->get();

This query is about half the speed of the first one.

Conclusion

I recommend using JSON for those fields where you feel the structure will change but don't know how. That's the first condition.

The second condition is that you will not perform where() operations on that column.

The third condition is that you shouldn't store too much data because of readability issues.

Generally, if you rely too much on the JSON format, you may encounter issues with how to read that data, ensure that it's correct, etc.

So, JSON is mainly for smaller fields, more like an exception to the relational database than something you should use more often.

We have some additional articles about JSON:

• Laravel: Why (Not) Use JSON DB Columns? Practical Examples.
• Laravel Custom Fields: JSON, EAV Model, or Same Table?