Storing JSON in relational DBs?

Question

PHP

Do you think it's normal practice to store some data in a database (Mysql) as JSON strings? Here's an example: We have 1000 posts that have pictures (10 pieces for each) and, for example, comments. And we need to display posts in a cycle (20 pieces) in this format: Post with photos and the last 3 comments. For example, how posts are displayed on the VK wall. Is it possible to make 2 additional cells (photos and comments) in the table with posts and store data in them in the form of JSON strings. There is a JSON string for photos and the same for the last 3 comments. Well, when changing or adding a photo or comment, for example, update these fields?
It seems to me that this will greatly reduce the load on the database, what do you think?

Answer 1

It makes sense sometimes to store JSON in some situations, but in such a case there is very little.
What if you want to do a search later on the comments, or on the photos. What will you do?
In your case, it is definitely necessary to separate all this into different tables. Make 1 additional request for a key (pull out photos) - this can’t be called a load for a database.
It's the same with comments.

Answer 2

If you do not need to search, sort, filter by fields from JSON, and you do not care about ACID, then this is a completely normal solution.

Answer 3

Take a look at PostgreSQL which has support for hstore and jsonb data formats (expected in 9.4, only 3rd beta now). Hstore is a single-level fast key-value storage, jsonb is already a hierarchical one that accepts and returns json, it can be used to build queries with queries.

Answer 4

1. This will increase the load on the database.
2. The decision itself is dictated only by savagery and monstrous ignorance. In a year or two, if you gain experience, then you yourself will look at this question of yours with horror.
The described structure is relational to the core. You don't need any Jasons here. If you make normal three tables, then any framework out of the box will provide controls (adding, deleting, editing) these comments and photos and whatever, with a single command . For a self-made jason, you will have to write the entire harness yourself, and then completely get confused in it.
If you take Laravel, then in the post model you will need to add the method

public function recentComments()
{
    return $this->hasMany('Comment')
        ->orderBy('created_at', 'desc')
        ->limit(3);
}

and the ENTIRE tape will be built by one statement, something like

return Post::orderby('created_at', 'desc')
    ->with([
            'photos',
            'recentComments',
        ]);
    ->limit(5);

Answer 5

If you are so impatient to get JSON from the database, then look through this .
In general, such a principle is good in itself (retrieving data in Json), but not in crooked hands. I used this in an online store to select products. Really more profitable in terms of query speed. Slightly slower than a regular select, but much faster than a whole series of queries. But in my opinion, only small pieces of data are appropriate to store in Json. Fragments of configs for example.

Answer 6

If you don't treat the json data in the table as a blob, this is a 1NF violation. The field must contain indivisible information. You can store. It can be processed on the client side, but it is impossible and wrong to use it normally at the relational database level.