JSONB, a structured format for storing JSON has been introduced in a message by Andrew Dunstan posted on PostgreSQL’s website. Andrew Dunstan authored JSONB together with Oleg Bartunov, Teodor Sigaev, and Peter Geoghegan; and reviewed by Andres Freund. Andrew described that this new format accepts the same data as the JSON type yet it is stored in a format that does not need reparsing the original text in order to process it. This process makes it more ideal for indexing and other operations wherein irrelevant whitespace is removed and order of object keys is not kept. Also, duplicate objects keys are not kept – the only one stored is the later value for a given key.
He also mentioned that this features was created out of the previous work by Oleg Bartunov and Teodor Sigaev which aimed to offer similar facilities to a nested HStore type, however, it was later on verified to have some major compatibility issues.
Meanwhile, Peter Geoghegan wrote in his blog that jsonb_hash_ops alternate GIN operator class by Alexander Korotkov, one of the contributor of JSONB not credited in the JSONB commit message, deserves an honorable mention. “By combining GIN with hashing of either key/value pairs, or array elements, resulting indexes can give great performance for sophisticated “containment” type queries against JSON documents. Indexes are a fraction of the size of the data indexed, index scans are incredibly fast, and yet these GIN indexes make indexable very complex nested “containment” queries. The results are so impressive that at last November’s pgConf.EU conference, an EXPLAIN ANALYZE comparative example in a presentation given by Oleg and Teodor was greeted with sustained applause.” Peter mentioned.
JSONB has made in the upcoming 9.4 release of PostgreSQL, an open source Object-Relational Database Management System (ORDBMS) known for its reliability, data integrity and correctness.
The following are developers’ reviews on JSONB and JSON:
Important usages of JSON and JSONB
- Use JSONB for quick JSON manipulation inside PostgreSQL such as sorting, slicing, splicing, etc.
- Use JSONB on indexed lookups for arbitrary key searches on JSON.
- Use JSON if you are not doing any of the above.
- Use JSON to preserve key ordering, whitespace, and duplicate keys.
Disadvantages of JSON and JSONB
- JSONB usually takes more disk space to store than JSON.
- JSONB takes more time to build form its input representation than JSON.
- JSON operations take considerably more time than JSONB (& parsing also needs to be done each time you do some operation at a JSON typed value).