One of the discussion points that came up was around the wonderful support for JSON, and JSONB in PostgreSQL. As part of that discussion, I started jotting down some points on the differences between the vanilla JSON, and PostgreSQL supported JSONB. These differences are described in the PostgreSQL documentation. I included the “implications” column, below.
I have not included the major advantages of going with JSONB over JSON, in the PostgreSQL context. Take a look at the documentation link for those details.
|jsonb||json||implications for developers|
|whitespace||discarded||preserved||semantically insignificant, and should not matter in
consuming the underlying data
|order of content||discarded||preserved||relying on json to retain order of content is a bad idea
|numbers print mode||Depending on underlying numeric type. ‘E’ notation may be
discarded, in favour of an expanded notation
|preserved||No loss of fidelity, but could be a problem if the ‘E’
notation representation is important.
|duplicate keys||Removed, and replaced with the last one||retains duplicates||Need to be aware of the unique-key constraint|
|Unicode content||If DB is UTF-8, then all characters in Json should be
correct Unicode characters. If DB is non-UTF, disallows Unicode
escapes for non-ASCII characters (those above U+007F)
|DB encoding does not matter; can store any Unicode character||Its probably a good practice to encode the DB with UTF-8, vs
|Primitive datatypes||Json types are mapped to PostgreSQL types||not mapped to PostgreSQL types||The underlying PostgreSQL type restrictions are applicable
to jsonb too.
|Numeric types||numbers will be rejected if the range is larger than the
postgresql numeric type range
|retains such numbers||The underlying PostgreSQL type restrictions are applicable
to jsonb too.
Another interesting article around this: http://pgeoghegan.blogspot.in/2014/03/what-i-think-of-jsonb.html.
tl;dr – “Still, like the earlier json type, jsonb in every sense “speaks JSON”. There are some limitations on what can be represented as a jsonb number, but those are exactly the same limitations that apply to the core numeric type (plus some limitations imposed by the JSON RFC, such as not accepting NaN values).”