PostgreSQL: json, jsonb support

I follow, and use the pydal, and web2py projects quite closely. I like the way these projects are engineered, and the community around them.

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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s