PostgreSQL® offers two types of data types to handle JSON data:
JSON
stores the JSON as text, performing a validation on the correctness of the JSON syntaxJSONB
optimizes the JSON storage in a custom binary format. Therefore, on top of validating the correctness of the JSON format, time is spent to properly parse and store the content.
NOTE: more info is available in the PostgreSQL JSON types documentation page
The following table defines the differences between JSON
and JSONB
data types in PostgreSQL.
Quality | JSON |
JSONB |
---|---|---|
Data storage format | raw text | custom binary |
Write | faster to write, only minimal JSON validation | slower to write due to binary conversion overhead |
Read | slower to read, reparsing needed | faster to read |
White spaces | preserved | removed |
Duplicated keys | preserved | only last valued is kept |
Order of keys | preserved | can be altered |
Index support | no | yes |
JSON item edit support | no | yes |
Review all the JSON PostgreSQL use-cases listed in the main page