Difference between JSON and JSONB in Postgres

json postgresql sqldatatypes jsonb
Somnath Muluk picture Somnath Muluk · Sep 22, 2016 · Viewed 23.1k times · Source

What's difference between JSON and JSONB data type in PosgresSQL?

  1. When should be used specific one?
  2. What's benefits or disadvantages with respect to other?

Answer

redneb picture redneb · Sep 22, 2016

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).

Related questions

How do I modify fields inside the new PostgreSQL JSON datatype?

With postgresql 9.3 I can SELECT specific fields of a JSON data type, but how do you modify them using UPDATE? I can't find any examples of this in the postgresql documentation, or anywhere online. I have tried the obvious: postgres=# …

Read More
How do I query using fields inside the new PostgreSQL JSON datatype?

I am looking for some docs and/or examples for the new JSON functions in PostgreSQL 9.2. Specifically, given a series of JSON records: [ {name: "Toby", occupation: "Software Engineer"}, {name: "Zaphod", occupation: "Galactic President"} ] How would I write the SQL to …

Read More
Query for array elements inside JSON type

I'm trying to test out the json type in PostgreSQL 9.3. I have a json column called data in a table called reports. The JSON looks something like this: { "objects": [ {"src":"foo.png"}, {"src":"bar.png"} ], "background":"background.png" } I would …

Read More