https://scotch.io/tutorials/working-with-json-in-mysql

SQL databases tend to be rigid.

If you have worked with them, you would agree that database design though it seems easier, is a lot trickier in practice. SQL databases believe in structure, that is why it's called structured query language.

On the other side of the horizon, we have the NoSQL databases, also called schema-less databases that encourage flexibility. In schema-less databases, there is no imposed structural restriction, only data to be saved.

Though every tool has it's use case, sometimes things call for a hybrid approach.

What if you could structure some parts of your database and leave others to be flexible?

MySQL version 5.7.8 introduces a JSON data type that allows you to accomplish that.

In this tutorial, you are going to learn.

1. How to design your database tables using JSON fields.
2. The various JSON based functions available in MYSQL to create, read, update, and delete rows.
3. How to work with JSON fields using the Eloquent ORM in Laravel.

#Why Use JSON

At this moment, you are probably asking yourself why would you want to use JSON when MySQL has been catering to a wide variety of database needs even before it introduced a JSON data type.

The answer lies in the use-cases where you would probably use a make-shift approach.

Let me explain with an example.

Suppose you are building a web application where you have to save a user's configuration/preferences in the database.

Generally, you can create a separate database table with the id, user_id, key, and value fields or save it as a formatted string that you can parse at runtime.

However, this works well for a small number of users. If you have about a thousand users and five configuration keys, you are looking at a table with five thousand records that addresses a very small feature of your application.

Or if you are taking the formatted string route, extraneous code that only compounds your server load.

Using a JSON data type field to save a user's configuration in such a scenario can spare you a database table's space and bring down the number of records, which were being saved separately, to be the same as the number of users.

And you get the added benefit of not having to write any JSON parsing code, the ORM or the language runtime takes care of it.

#The Schema

Before we dive into using all the cool JSON stuff in MySQL, we are going to need a sample database to play with.

So, let's get our database schema out of the way first.

We are going to consider the use case of an online store that houses multiple brands and a variety of electronics.

Since different electronics have different attributes(compare a Macbook with a Vacuumn Cleaner) that buyers are interested in, typically the Entity–attribute–value model (EAV) pattern is used.

However, since we now have the option to use a JSON data type, we are going to drop EAV.

For a start, our database will be named e_store and has three tables only named, brands, categories, and products respectively.

Our brands and categories tables will be pretty similar, each having an id and a name field.