Difference between BSON in Mongodb and JSONB in PostgreSQL

JSONB (PostgreSQL's Binary JSON) and BSON (Binary JSON, primarily used by MongoDB) are both binary serialization formats for JSON-like documents, designed to be more efficient for storage and querying than plain text JSON.

While they share the same goal, they are distinct specifications, each optimized for its respective database environment.

---

Key Differences

Feature	JSONB (PostgreSQL)	BSON (MongoDB)
Primary Use	Efficient storage and indexing of JSON data within a relational database (PostgreSQL).	The native storage and wire protocol format for the MongoDB document database.
Data Types	Primarily supports standard JSON types, with internal mapping to PostgreSQL's native types (e.g., numeric for numbers, not a universal "number").	Extends standard JSON with specific, non-JSON types like Date, Binary, ObjectID, and multiple explicit numeric types (e.g., Int32, Int64, Decimal128).
Canonicalization	Aggressive. Removes duplicate keys, discards white space, and does not preserve key order.	Less Aggressive. Preserves key order and may include length metadata for fields.
Arrays/Lists	Stores them as true JSON arrays ([...]).	Stores them essentially as ordered key-value maps with numeric keys (e.g., {"0": "a", "1": "b", ...}).
Indexing	Uses PostgreSQL's GIN (Generalized Inverted Index) for indexing keys and values within the JSON document.	Uses native indexing capabilities (e.g., B-tree) directly on fields within the BSON document.
Storage & Size	Can be less space-efficient than BSON for some documents because it serializes field names with every document.	Often more space-efficient, particularly for large documents, due to its design, which includes length/type information for fast traversal.

---

Detailed Breakdown

1. Data Type Support (The Biggest Difference)

• BSON adds explicit data types that are critical for a database, such as:

  • Date/Time: Stored as a 64-bit integer, allowing for fast, indexed date queries without string parsing.
  • Binary Data (BinData): Allows direct storage of raw byte arrays (like images or encrypted data).
  • ObjectID: MongoDB's 12-byte unique identifier type.
  • Explicit Numbers: It differentiates between Int32, Int64, Double, and Decimal128.

• JSONB maps JSON's generic data types to PostgreSQL's existing rich types. While PostgreSQL can handle dates, binary data, etc., these are not native types within the JSONB structure itself, but rather handled by the surrounding database environment.

2. Canonicalization and Order

• JSONB fully normalizes the JSON data on input. This means the original formatting, including whitespace, key order, and even multiple instances of the same key, is not preserved. This makes query performance more predictable.
• BSON preserves key order. This can be an important distinction for applications where the order of keys (fields) within an object is semantically significant.

3. Database Context

The formats are tied to their respective database ecosystems:

• BSON is the cornerstone of MongoDB. It was designed from the ground up to be the native, efficient storage and network transport format for a document database.
• JSONB is an extension for PostgreSQL that allows a powerful relational database to efficiently handle semi-structured data, combining the flexibility of JSON with the robust indexing, transactional, and relational features of Postgres.