72

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?
Somnath Muluk
  • 55,015
  • 38
  • 216
  • 226
  • 1
    Another overview: http://stackoverflow.com/questions/10560394/how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype/10560761#10560761 – Erwin Brandstetter Sep 22 '16 at 14:01
  • 3
    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. – Kashif Jan 16 '20 at 03:32
  • It's already answered somewhere else but the title of this entry is better to find my solution! – Burcin Apr 12 '22 at 06:11

2 Answers2

99

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

redneb
  • 21,794
  • 6
  • 42
  • 54
48

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
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Piotr Rogowski
  • 3,642
  • 19
  • 24