What's difference between JSON and JSONB data type in PosgresSQL?
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).