Menu
What's difference between JSON and JSONB data type in PosgresSQL?
909
Jsonb stores the data as binary code. Basically, it stores the data in binary form which is not an ASCII/ UTF-8 string. Json preserves the original formatting like the whitespaces as well as the ordering of keys. Jsonb does not preserve the original formatting of text like the whitespaces and the ordering of keys.
Significantly faster to process. Supports indexing (which can be a significant advantage, as we'll see later). simpler schema designs (replacing entity-attribute-value (EAV) tables with JSONB columns, which can be queried, indexed, and joined, allowing for performance improvements up until 1000X!)
JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
The jsonb datatype is an advanced binary storage format with full processing, indexing and searching capabilities, and as such pre-processes the JSON data to an internal format, which does include a single value per key; and also isn't sensible to extra whitespace or indentation.
json is basically a blob that stores JSON data in raw format, preserving even insignificant things such as whitespace, the order of keys in objects, or even duplicate keys in objects. It does offer the ability to do some basic JSON operations such as extracting the value associated with some key in an object, albeit it is slow at that since it has to parse the JSON blob every time. It also validates every value to check that it is valid JSON. jsonb on the other hand stores JSON data in a custom format that is optimized for certain operations such as extracting the value associated with some key in an object (i.e. it will not reparse JSON, it will not search linearly). Additionally, jsonb supports more operations, just as concatenation of objects or setting a value deep inside an object.
In general, I use json only if I know that I will not do any JSON operations or only do them occasionally. For all other cases I use jsonb. Note that for the former case, text it is also a perfectly valid option, especially if you are not interested in the validation that json does (e.g. because you trust the source of the data).
104
This is explain: https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/
In most cases JSONB is likely what you want when looking for a NoSQL, schema-less, datatype. Hstore and JSON can have their place as well but it’s less common. More broadly, JSONB isn’t always a fit in every data model. Where you can normalize there are benefits, but if you do have a schema that has a large number of optional columns (such as with event data) or the schema differs based on tenant id then JSONB can be a great fit. In general you want:
JSONB - In most cases JSON - If you’re just processing logs, don’t often need to query, and use as more of an audit trail hstore - Can work fine for text based key-value looks, but in general JSONB can still work great here
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
