# PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON

DevFeed: [PostgreSQL jsonb: Internal Representation, JSON Compatibility, and Differences from BSON](<https://devfeed.tech/articles/what-i-think-of-jsonb-33649.md>)

Original publisher: [Read original article](<https://pgeoghegan.blogspot.com/2014/03/what-i-think-of-jsonb.html>)

Author: Peter Geoghegan (noreply@blogger.com)

Published: 2014-03-24T01:39:00Z

Content type: opinion

Language: en

Sources: [Peter Geoghegan's blog](<https://devfeed.tech/sources/peter-geoghegan-s-blog.md>)

Topics: [JSON](<https://devfeed.tech/topics/json.md>), [Databases](<https://devfeed.tech/topics/databases.md>), [SQL](<https://devfeed.tech/topics/sql.md>), [MongoDB](<https://devfeed.tech/topics/mongodb.md>)

Tags: [c](<https://devfeed.tech/tags/c.md>), [internals](<https://devfeed.tech/tags/internals.md>), [json](<https://devfeed.tech/tags/json.md>), [jsonb](<https://devfeed.tech/tags/jsonb.md>), [mongodb](<https://devfeed.tech/tags/mongodb.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [sql](<https://devfeed.tech/tags/sql.md>)

## AI overview

The article examines PostgreSQL's jsonb data type, including its binary and internally typed representation, indexing of nested predicates, numeric comparison behavior, duplicate-key handling, and compatibility with textual JSON. It argues that jsonb is distinct from MongoDB's BSON and discusses limitations anticipated by JSON RFC 7159.

## Source excerpt

Unsurprisingly, there has been a lot of interest in the jsonb type, which made it into the upcoming 9.4 release of Postgres. I was initially a reviewer of jsonb, although since I spent weeks polishing the code, I was ultimately credited as a co-author. Jsonb is a new datatype for Postgres. It is distinct from the older json datatype in that its internal representation is binary, and in that it is internally typed. It also makes sophisticated nested predicates within queries on jsonb indexable. I've occasionally described the internally-typed scalar values as having "shadow types" unknown to the core SQL parser. This has several implications. For example, if you sort two Jsonb values containing only scalar numbers, the implementation invokes the numeric comparator (which the jsonb default B-Tree opclass comparator is defined in terms of). The on-disk representation of jsonb includes the same representation as is used for, say, numerics (as the internal binary representation of JSON primitive numbers, for example). Plus, JSON objects are de-duplicated by key on input, and optimized for cheap binary searches within a single jsonb. 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). I hope it suffices to say that these limitations are virtually irrelevant, and that many implementations have similar or worse limitations. All of these minor implementation-defined restrictions are explicitly anticipated and allowed for by the recent JSON RFC-7159. Jsonb is emphatically not like the BSON format used by MongoDB. That format accepts input in such a way as to be backwards compatible with JSON, but I believe that BSON isn't really a practical interchange format, because the software development community at large is presumably d