CODEWITHSUNDEEP
postgresqlsqldatatypes
Ajx
Ajx

Reputation: 323

JSON vs JSONB Postgresql

I am reading about differences between JSON and JSONB datatypes on PostgreSql documentation https://www.postgresql.org/docs/13/datatype-json.html.

There is this line

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

I am not able to understand what is the difference between storing as text and storing as binary format is, the string itself will be stored as as sequence of 0's and 1's.

Can somebody please clarify? Also, will there be a size difference between them?

Upvotes: 16

Views: 22560

Answers (2)

Bergi
Bergi

Reputation: 664528

json is essentially stored as text, which needs to be parsed every time you operate on it. This means it does preserve whitespace formatting and allows peculiarities such as duplicate property keys in objects.

jsonb is an optimised binary format that represents the tree structure of nested arrays and objects, which is possibly smaller (idk) slightly larger to store but faster to access and operate on.

Upvotes: 32

Simon Martinelli
Simon Martinelli

Reputation: 36143

As it is written in the documentation it is faster because it's an optimized format for JSON.

The data types json and jsonb, as defined by the PostgreSQL documentation,are almost identical; the key difference is that json data is stored as an exact copy of the JSON input text, whereas jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.

Please read: https://www.compose.com/articles/faster-operations-with-the-jsonb-data-type-in-postgresql/

Upvotes: 1

Related Questions