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 dataBut 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...
Premium Members Only
This advanced tutorial is available exclusively to Laravel Daily Premium members.
Already a member? Login here
Premium membership includes:
Comments & Discussion
1NF - a single field must not hold more than one value (atomicity)
Someone who added json type to databases should burn in hell.
This is what I usually do.
In every JSON that are several fields that I may need to use for searching, so I can create a table having those fields and file_prefix (or file_path) column.
The table also contains standard fields like id, user_id etc...
Fields that hold values extracted from JSON in 99% of cases can be indexed and nullable. This allows for efficient searching.
I store JSON content in files uploaded to external storage like s3. Since I always run everything in the same cloud infrastructure and same region, there is almost no latency when grabbing files from storage. ......................................................................................................................................................
In theory I instead of storing json in file I could have just a text filed called json_content which would store a single value - a json content. But some of those jsons may be quite big, thus I store them in files.
Hi, in this video, the json column is performing similar to single columns for about 1000 rows. I have an online store which uses the db structure as above with $product->attributes->size->M with attributes being the json column. I have created a virtual column pointing to attributes->size on which the query runs. Is this approach good for performance?
Great article, thanks a lot! For a project I'm working on, I built a flexible scraper. As we don't know what kind of data is coming in, for example how many open graph tags, if there are any or the number of images with suitable size on the page are found, I store these values in JSON columns. Luckily I don't have to search in these columns, it's simply for storing the data. Later on the data gets transformed into another model to be used. So for example the open graph image becomes a Media item, attached to the item unless there is none in which case, a suitable image from all images collected from the page is selected. Just wanted to share my use case and that I absolutely agree to be careful when to use JSON columns.