SQL JSON Vs JSONB Datatype

In this article, we'll look at what JSON and JSONB are, as well as the differences between the two.

SQL JSON Datatype

PostgreSQL JSON is a JSON data type in PostgreSQL that is used to store data as text with values written according to JSON principles.

JSON stands for "Javascript Object Notation."

The JSON data type is essentially a blob that saves JSON data in raw format, maintaining even minor details like whitespace, object key order, and even duplicate keys in objects.

It has limited querying capabilities and is slow due to the fact that it must load and parse the complete JSON blob every time.

SQL JSONB

PostgreSQL jsonb , where jsonb stands for 'Javascript Object Notation Binary,' is a JSON data type used to store data in binary format in PostgreSQL.

JSON data in a custom format that is optimised for querying and will not reparse the JSON blob each time JSON data is first converted and then stored.

So the data input is slightly slower than JSON but the processing speed in binary form is much faster, so overall efficiency in the case of JSON data type is excellent.

SQL Difference Between JSON AND JSONB Datatype

There are 9 main constrast in json Vs jsonb datatype :-

JSON	JSONB
JSON saves an exact copy of the data represented to the user in JSON format.	JSONB saves data in binary format, which means that the data is processed first and then saved in binary format.
JSON maintains the original formatting, including whitespaces and key ordering.	The original formattiang of text, such as whitespaces and key ordering, is not preserved by JSONB.
Because there is no conversion involved, JSON processes input faster than jsonb.	Because JSONB translates JSON data to binary, it has a significantly slower input speed due to the binary conversion overhead.
When working with JSON, the Schema design does not alter. All entities, attributes, and values are unchanged.	Schema designs are simpler than Json because entity-attribute values are replaced with jsonb blob columns, which are easier to query.
JSON data does not take up a lot of disc space because it is kept in its raw form.	When compared to JSON data, jsonb data requires more storage space.
When the input text is of JSON data format, the processing function must be reparsed after each run.	Processing data using jsonb is comparably faster because no reparsing is required.
Unlike jsonb, JSON does not support indexing.	JSONB documents support indexing to search for key/value pairs, which is a huge benefit when dealing with a large database of jsonb documents.
In JSON, if a particular value has more than one key, all of the key/value pairs are retained.	Duplicate object keys are not preserved by JSONB. Only the last key/value pair is kept when there is a duplication.
Regardless of the data encoding, Unicode escapes are allowed in the JSON input function. It just looks for grammatical faults.	If the database encoding is not configured to UTF8, the jsonb input function is harsher than JSON, as it does not allow unicode escapes for non-ASCII characters.

All Tutorials