JSON in PostgreSQL

SQL/JSON path support was added to Postgres 12.

PostgreSQL 12 features ^postgres12released (opens in a new tab):

PostgreSQL: PostgreSQL 12 Released! Visited 2022-07-02

Things to Avoid: JSON Anti-Patterns

As with everything in computer science, JSON is not a silver bullet. It adds more flexibility to the relational data model, but there are still some JSON anti-patterns to be wary of:

  • Modeling relational data. JSON is not a replacement for row stores. JSON is still significantly slower than regular row based data because of lack of statistics. This is a known limitation if you are planning to use JSON for analytical queries as it is simply impossible to build statistics for a schema less architecture.

  • Replacing NoSQL. JSONB is still not a replacement for NoSQL systems [...].

  • Size. JSONB content and GIN indexes take a lot more space [...] and it is hard to table partitioning when compared to row based data. So one should be very mindful of the data scale as PostgreSQL cannot horizontally scale like other NoSQL database systems.

  • Joins. It is difficult to do normalization with one-to-many, many-to-many relationships with JSON type. JSON not meant for normalized data and doing joins is an anti-pattern and will lead to performance problems.

-- Forget SQL vs NoSQL - Get the Best of Both Worlds with JSON in PostgreSQL https://arctype.com/blog/json-in-postgresql/ (opens in a new tab)

References

END.