JSON Fields in Laravel DB: 2 Cases to Review

If your DB structure seems quite large and unpredictable, maybe it's worth storing the data as a JSON column? Let's take a look at a few examples.

- - - - -
Support the channel by checking out my products:

- - - - -
Other places to follow:

[farixromero]

Man you are so good at explaining. Cheers from Perú.

[AndRyu]

Agreed. Just started now using json fields for a survey application, but only to store the question's options (list for closed ones, validation for open ones, and so on..)
The answer is stored directly as a value, solved the issue of having extra detail table or multiple fields that would be unused

[princehusky]

I was always wondering how to structuring dynamic survey questions and store it to database so looking forward to buy your course!

[КириллКоншин-гк]

Just used json field for storing additional user data, which intended to be sent to monolog logger 'extra' property. Seems suitable.

[BelgranoK]

In a project I work we used a json field to store the all the data needed to build a report. We thougth that was useless to run all the queries to get the data needed to render the report.

[spartacusyusuf]

I did use json fields for a project that has to do with bitcoin payment gateway and i needed saving the response from webhook for later reference and its helpful for admin to show users info about a transaction. It is also helpful to show users time spent in carrying out the transaction process and lot more

[intipontt]

There is a package for auditing database changes (using eloquent events): owen-it/laravel-auditing. In that package, there is a migration for a table called 'audits'. That table, among other things, has 2 particular columns: new_values and old_values. Those are json fields. It works really well for that use case and the amount of configuration you can do to it is pretty extensive.

[saurabhshukla]

Agreed. Relationship data is enough. I have implemented this years ago. Both Survey and Quizzes.

[marcusgaius]

Good example of a json field use case is image exif data, although, sometimes specialized apps may want to have it extracted. But in general, it seems like a good candidate.

[popetgirl]

I recently used json field for QR code generation i am saving QR properties in that json column, background color, foreground color, border size and space. Which is helpful instead of creating 4 different columns.

[spartacusyusuf]

Also used it for realtime data pull from trading view websockets where i needed pulling currency rate and it contain lp, chp and about 15 other keys. So i had to save in json

[coolcha]

Another reason to avoid JSON field from my experience has been when the data present in the them will be replicated across many rows. For example, we were storing multi-author publications in our DB previously as JSON fields and slowly realized that many authors had "overlapping" publications, hence the same publication information would be replicated across different users. In this case, we refactored our code and converted the JSON column to separate publications table with a many-to-many relationship to the User model.

But believe me JSON still has its uses for certain fields.

[janfornication]

An interesting use case for json field could be translations { en_US: "Soccer", de_DE: "Fussball" }

[niki.mihaylov]

Hey Povilas, many thanks for this video! It was for sure helpful with your thoughts :)

Another option that I have in mind is to setup different sqlite database per survey but the point is that I have no idea of how to add dynamic connection per survey. What are your thoughts on this? Will it make the data exporting/comparing/reports much faster with only the survey data there?

[__pasty]

​ @LaravelDaily Okay so let's look at this scenerio.

I have table columns

contact -> string -> index
documents->json

Query

$result = $this->where('contact', $contact)->whereIn('documents', $document_array)->get();

Question

I'm running where condition on two columns with one indexed, and the other not. Will this increase performance since one column is indexed or it will loop through all the columns??

[afuwapesunday]

Another user case is an hotel booking where you need to store room amenities

[robdorn]

I wonder if in many of these cases, like a survey, a no-sql db like MongoDb would be more suitable than a relational db.

[noplay]

What about saas applications and addresses for example. Customer A only needs firstname & lastname but Customer B also need street. Obviously you could just use null for customer a in street name. But with a lot customers and their own needs you would have around 100 extra fields.

[marcusgaius]

From reading the email, you get the impression it's just a typical multi-tenancy structure, or very closely adjacent.

[nabeelyousafpasha]

After so many videos, may I know your setup gears? What is your Mic, is it rode? What do you use for screencast?
Respect from Pakistan 🇵🇰