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 syntaxJSONBoptimizes 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
