PostgreSQL® offers two types of data types to handle JSON data:
JSONstores the JSON as text, performing a validation on the correctness of the JSON syntax
JSONBoptimizes 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
JSONB data types in PostgreSQL.
|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|
|Duplicated keys||preserved||only last valued is kept|
|Order of keys||preserved||can be altered|
|JSON item edit support||no||yes|
Review all the JSON PostgreSQL use-cases listed in the main page