Laravel: Why (Not) Use JSON DB Columns? Practical Examples.

JSON columns in the database seem like a solution if you're not sure about the structure of data. But quite often, they lead to more problems in the future. In this article, I want to show both good and bad cases of using JSON columns with Laravel examples.

If I had to summarize it all in one sentence, it would be this.

Don't use JSON columns if you need to search within that data later.

To rephrase this, it's ok (-ish) to use JSON columns if you just get a single DB record and then process data within that record.

In other words, this isn't good in most cases:

// Migration:
$table->json('options');
 
// Controller:
$users = DB::table('users')
    ->whereJsonContains('options->languages', 'en')
    ->get();

And this is good:

$user = User::find($id);
// Do JSON manipulation in Laravel from $user->options data

But it's not so black and white. There are various examples. So, let's get practical.

Legit Cases to Use JSON Columns

These are real-life examples of JSON columns that I've found.

Legit Example 1. Translations.

Packages like spatie/laravel-translatable store data in JSON and then get the translations for a single record with this Laravel code:

$newsItem->getTranslations('name');
// returns ['en' => 'Name in English', 'nl' => 'Naam in het Nederlands']

I would argue with myself this is a semi-legit example because if you need to search for some text in some language, the DB query with raw JSON function may be relatively slow:

NewsItem::whereRaw("JSON_EXTRACT(name, '$.en') = 'Name in English'")->get();

Legit Example 2. Packages with Unknown Properties.

Let's take a look at another well-known Spatie package: laravel-medialibrary. For managing media files, it stores some of the data in an unstructured way, as JSON columns.

We can see these columns in the migration of the media table.

Schema::create('media', function (Blueprint $table) {
    $table->id();
 
    // ... other fields
 
    $table->json('manipulations');
    $table->json('custom_properties');
    $table->json('generated_conversions');
    $table->json('responsive_images');
});

Quite a few JSON columns, right? But the main thing is that there's no search for specific values in specific keys of those JSON columns.

For example, the custom_properties field is used when saving individual Media file and when retrieving its data, like adding filters:

protected function getDefaultFilterFunction(array $filters): Closure
{
    return function (Media $media) use ($filters) {
        foreach ($filters as $property => $value) {
            if (! Arr::has($media->custom_properties, $property)) {
                return false;
            }
 
            if (Arr::get($media->custom_properties, $property) !== $value) {
                return false;
            }
        }
 
        return true;
    };
}

As you can see in this example, the package works with one object $media and doesn't search for values in that JSON column of custom_properties.

JSON columns often make sense in Laravel packagesbecause package creators don't know which properties package users would want to store. Also, in the future, package authors may decide to store other properties.

More Examples: Flexible or 3rd-Party Structure.

Here are a few more examples of their use cases that people mentioned to me on Twitter:

• "Form builder I created that uses them for the structure, I use them for managing template settings for a page builder"
• "Store design templates / settings"
• "For “meta” type information from a third party"
• "My client wanted to create many forms, with different fields for campaigns."
• "GPS trackers so we use JSON to store tacker data because every model of GPS send diffrente data and to be ready in future to add new GPS model without changing table and code"

I haven't seen those projects myself, but in most of those cases, I assume that developers don't query the JSON data directly but work with individual records, like a single form, a single design template, or a single GPS coordinate.

Not-so-legit Examples: Forum Questions

If you want to find cases of inappropriate JSON columns usage, it usually looks like this:

1. I will store the data in a JSON column. Why bother with the DB structure?
2. Hmm, what is this weird syntax of querying?..
3. Why can't I easily query XYZ inside JSON?
4. Laravel is crap. Why doesn't it work well with JSON??!!!

You can find multiple examples of this on forums. Here are a few random screenshots.

Example 1:

Example 2:

Example 3:

So, to avoid dealing with those situations, you should consider NOT using JSON columns in the first place if you predict you would need to query the data in the future.

Instead, read this post by Patrik that is often referenced on Laracasts forum: The value of a good database model

JSON Functions That May Help

Ok, but if you did end up using JSON columns, here are a few things that may assist you.

Helper 1. Eloquent Casting to Array

Laravel has a way to serialize and deserialize JSON values using casts automatically.

class User extends Model
{
    protected $casts = [
        'options' => 'array',
    ];
}

After adding this cast, the $user->options will automatically be transformed into an array.

You can check the complete example in the official Laravel documentation.

For more complex applications, casting to array might not be enough. In such cases, casts are made using custom cast implementation AsArrayObject and AsCollection.

You can read about them in the official Laravel documentation.

Helper 2. Laravel JSON Where Functions

You can use the where() method for the JSON column using the column->json_key syntax.

For example, if I have a DB column data with values like {"name": "water"} and {"key": "watermelon"}, I could search for them like this:

Model::where('data->name', 'LIKE', '%wat%')->get()

This works because Laravel builds the raw SQL query and adds json_unquote() and json_extract() MySQL functions to the query:

select * from `json_data` where json_unquote(json_extract(`data`, '$."key"')) LIKE '%wat%'

There are also Laravel functions like whereJsonContains() and whereJsonLength(). Read more about those functions in the official Laravel documentation.

What about ordering? Can you do orderBy() with the JSON column? Yes, you can do ->orderBy('column_name->json_key) and it would "kinda" work. BUT there is a big chance that the result will be incorrect. This is because of how MySQL treats blobs. To order correctly, you need to use orderByRaw and CAST. For example:

->orderByRaw('CAST(JSON_EXTRACT(json_column, "$.json_key") AS unsigned)', 'asc')

You can read more about blobs in the official MySQL documentation.

Helper 3. Raw Queries with MySQL Functions

Often when using RAW queries for the JSON columns, you will have to use specific MySQL functions explicitly made for JSON.

Perhaps you don't want to use just where() from Laravel, but build raw queries yourself, then use one or more of these MySQL methods:

• JSON_CONTAINS()
• JSON_CONTAINS_PATH()
• JSON_EXTRACT()
• JSON_KEYS()
• JSON_OVERLAPS()
• JSON_SEARCH()
• JSON_VALUE()

And it's not only about querying the data but also about getting the result in the right way. For example, when selecting JSON values, you would do something like ->select('json_column->key as result'), BUT this would return result in quotes. So to remove the quotes, you need to know the unquotiung extraction operator. Instead, that select should look like ->select('json_column->>key as result'). The operator is ->>.

Read more about those functions in the MySQL documentation.

You can also learn more about JSON and MySQL in general in the MySQL for Developers by Aaron Francis.

Helper 4. Indexing Column

One of the popular ways to increase performance is indexing. MySQL doesn't support indexing JSON. But there are two ways how to index by the key.

Method 1: Separate Column With Index

The first method works with MySQL 5.7 and 8. Here we need to generate a new column and index it.

ALTER TABLE json_data ADD COLUMN email VARCHAR(255) GENERATED ALWAYS AS (`json` ->> '$.email');

Here, we create a new email column with a value corresponding to the json column and email key.

And then, we add an index to the email column.

ALTER TABLE json_data ADD INDEX (email);

Method 2: Function-based Index

The second method is creating a function-based index that only works with MySQL 8. Using this method, we skip the intermediate column and put an index directly on the function itself.

ALTER TABLE json_data ADD INDEX ((
    CAST(`json`->>'$.email') AS CHAR(255) COLLATE utf8mb4_bin)
));

In Conclusion

I still stand by my opinion that you should use JSON columns only in rare cases where you really don't know the structure that would be saved there. When you absolutely need flexibility.

Also, if you predict that this column will be searchable a lot, that's also a red flag against the JSON structure.

But, in some cases, it's still a valid solution, and with Laravel/MySQL helpers functions, it's pretty convenient to use if you choose to.