I’m new to Databases and I started using PostgreSQL recently, I was wondering where, when and why should I choose Json and Jsonb type for my columns? In which cases using Json type is better and in which cases it’s not?
I appreciate any help
Question posted in PostgreSQL
The official documentation can be found here.
2
Answers
JSON data types are for storing JSON (JavaScript Object Notation) data. A practical use could be for storing API responses for subsequent processing.
The json and jsonb data types accept almost identical sets of values as input. The major practical difference is one of efficiency. The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed. jsonb also supports indexing, which can be a significant advantage.
As appears in official docs.
If the software client wants/demands to send JSON to the database, and to get JSON back from the database, then it might be a good idea to store JSON in the database. If you are getting something which is not JSON, and need to send back something which is not JSON, then it almost always going to be bad idea to store it in the database as JSON(B) while converting it in and converting it out.