Laravel JSON DB Columns: Worth Using Them?

A video from my upcoming course on structuring Laravel DB. Let's discuss JSON column types and their usage.

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

[Laratips]

Nice video. I used the json column to store the response from the payment gateways. Different payment gateways have different responses, so it's handy in this case.

[creeddm]

JSON columns are are great for storing log data. Like all properties changed on the model for example. laravel-activitylog package from Spatie uses that

[Jurrevanderschaaf]

Great video! It would also be interesting to do the same comparison with a PostgreSQL database with JSON data in it. MySQL does not support indexing JSON data, where PostgreSQL does. That will probably partly narrow the differences in query times

[plpGTR]

If I request data from an external API and I need to keep track of it like daily log and end of month-analysis I usually keep the raw response in JSON since more fields might be required which I did not know in the beginning. But then I extract the actually used data to columns which use indexes then. This helped me a lot with reporting over time with external apis.

[caoimhinj]

I use JSON columns to cache external API payloads that provide "default" properties for my models via getters. I don't typically need to search through these fields, so it works great for the use case.

[rolandosanches]

I used it in a document management project. In this project the user can create different types of documents and indicate what information (metadata) he wants to extract from those documents. For this, the application allows the user to indicate these fields and their type (text, number, date ...). This form is recorded in a JSON column. When entering a new document, the user indicates its type, then the application reads the JSON associated with this type and designs the form. The collected data is also stored in JSON (in another table, which relates to the table that stores the type data).

When viewing a document, the application reads the type table and draws the form and then reads the response table and fills in the fields. So far I have had no problem.

I solved the JSON search problem by creating a text column that stores search information. When searching, the search is done in this column and not in JSON, which increases performance.

In a simplified way, this is it. In practice it is a little more complex, since both documents and types of documents have versions. So the application has to be concerned not only with loading the correct type of document, but also with the correct version.

[gram_o_phone]

At some point I always had to convert json fields to relational, except kind of logs or history. Eg. to get the stock history for a product without querying all the orders and supplies, it works very well. The properties example is great, because it is a certainty at some point you want to filter them, so go with relational.

[itachinight]

Been using json fields storing logs and notifications data and it works great for this purpose

[ricko]

always good content, I needed to know the disadvantages before implementation 👍

[mattburgess]

I’ve used JSON fields for addresses, especially international ones. This lets you store whatever the address convention is, rather than trying to normalise it, which is practically impossible.

Another good case is some kind of event log where the properties are arbitrary or dynamic.

[Narkboy]

I worked on an app that imported a lot of data from a variety of Excel formats and used several JSON fields to store the specifics of the Import process. It worked well for allowing any kind of Import to be stored in one table but there were two clear issues. Firstly, the flexibility sometimes translated into lazy code as devs just stuffed whatever into the JSON field - you have to have a very clear rule about what is and isn't suitable. Second, the database size exploded because larger imports were adding far, far too much data to each row. These aren't issues with the actual JSON columns, but they are really easy pitfalls to drop into; I still use them but strictly for very specific use cases - like international addresses as mentioned.

[nmaniacos]

In laravel nova, for NovaFlexibleContent package. Also very useful for storing log data.

[MiguelRamirez-njjl]

Hi I´m from Cuba and I work with Adonis wich is almost identical to Laravel and i learn many useful things in here, and the most of then work in Adonis as well.

[iukhan]

Wow, i absolutely love your videos.
REQUEST: Can you please make a video on repository and criteria based models? i have no idea how they works and have seen them in multiple projects now.

[Heatedpillow]

First thing that comes to mind here - spatie translatable. They only have json fields for translations saving without option of using separate model. And... it is popular with 1+ milion downloads. I’m still thinking if i should use it 😀

[dariusdxd]

JSON database field can be very useful when need to save data temporally while user is working and don't want to add / update real data. So next time he can load data from JSON field to inputs and continue working until he will decide to publish data and put in specific tables.

[guillermocava]

Recently did a custom form builder, the ui let you create a form, create inputs, type of input, if the input had options (like radio, checkboxes, select dropdown), andy validation rule and then show the submissions to that form.

I went withthe json approach since the complexity later for the submissions was also pretty high, and since this custom form builder wasn't the main feature of the app, going full relational would have added several more tables and logic.

An example of the schema for relational

[poplach]

I use text fields to store json or serialized properties of something. Never actually bother to create a json column, text works just fine since I never have to query with these params.

[murilolivorato]

very good . I want to make a question .
can I store in mysql, in json field .
Array instead objects ?
intead {'car': 1, 'bus': 1} can I store ?
['car', 'bus']

[mdprotacio]

I wonder if laravel has something that can utilize generated column from json field. We use it quite a lot for indexes and for searching through json data.