Is it ok to store json in MySql?

Question

MySQL

Hello. I am working on a website and the site has a user registration function, when registering, email, login, pass are indicated as standard, but in addition to this, a lot of different data is indicated - full name, age, country, city, phone number , etc. Should this data (name, age, country, etc.) be stored in the database in JSON format or not? ps in the future, this data will be sorted.

Answer 1

Theory
Relational bases imply that all frequently used fields should be stored in separate columns. At some point, you will need to search/sort by city, and storing it inside JSON will make this operation extremely difficult.
All the advantages of JSON come down to the fact that in one record you can "easily" add or remove some field without resorting to modifying the table. Ease is written in quotation marks because modifying one field is more difficult than writing the entire object initially: take JSON; convert to object; modify the value of the required field; convert to string; write it to the database. And this will need to be done by means of the application, MySQL is simply not capable of this.
Using JSON is normal practice. If there is a need to search for a certain field, it is placed in a separate column. Porting work is routine, but requires attention from the programmer, because. the path to the value has changed. For example, it used to be user.data.city and became user.city .
Now PostgreSQL is gaining popularity, where work with JSON is brought to the level of SQL syntax. There you can easily add/modify/delete individual JSON fields without the help of the application. Even indexes are supported!..

Answer 2

The posing of the question is interesting - is it normal to blablabla.
First, do as you think, store dynamic fields in the json field of the table row. Then when you get tired of writing sql queries to extract these fields, you yourself will understand what you need to store where. Whatever solves your particular problem is fine.
Postgresql knows how to work with json, but you have to write deserializers and serializers for these fields when saving and retrieving. I don't like it, additional code.
Look for interest what Mongo can do. Now they will criticize me, like a person works with relational databases, but if you are not familiar with it, then I think this will be useful information

Answer 3

ps in the future, this data will be sorted.

If there is JSON - no, it won't.
Or take Postgres.

Answer 4

json will be stored as one line. And in order to sort by city, you will need to somehow extract the value from this string. If I were you, I would not complicate my life, but store it in separate fields. In the future, you can work with this with ordinary sql commands ...

Answer 5

no
better in postgres

Answer 6

how are you going to sort them if you have them json ?
it is logical that it is necessary to store natively