What are the benefits of storing columns in JSON instead of traditional tables?

Question

Are there any benefits in using JSON(B) over traditional table structures?

Imagine having a table structure like this:

 create table table1 (
        t_id int,
        first_name varchar(20),
        last_name varchar(20),
        age int
    )

What if you stored the same columns inside a JSON(B) field like this:

{
    "first_name":"name",
    "last_name":"name",
    "age":2
}

and have a table like this:

create table table2 (
    t_id int,
    attribute jsonb
)

Correct me if I'm wrong, but since both variants are causing a row to be completely rewritten if there have been any updates or deletes on that row, then both variants are identical in that regard.

Answer 1

Large complex models can be stored (read and write) in database with low cost. You are avoiding expensive joins and other db operations.

Out of the box serialization/deserialization in your app, you can avoid ORM completely.

You get flexibility to change model withouth changing database schema.

On the other hand you are losing db mechanisms like normalization, referential integrity, data type checking ...

Querying json data is not impossible, but it's slower than querying relational data.

Use case scenario is data that has been exclusively manipulated by your app.

Answer 2

If you really want to be able to add as many fields as you want with no limitation (other than an arbitrary document size limit), consider a NoSQL solution such as MongoDB.

For relational databases: use one column per value. Putting a JSON blob in a column makes it virtually impossible to query (and painfully slow when you actually find a query that works).

Relational databases take advantage of data types when indexing, and are intended to be implemented with a normalized structure.

As a side note: this isn't to say you should never store JSON in a relational database. If you're adding true metadata, or if your JSON is describing the information that does not need to be queried and is only used for display, it may be overkill to create a separate column for all of the data points. Check this one for detailed explanation