pgbson

pgbson : BSON data type and accessor functions for PostgreSQL

Overview

Packages

Source

pig build pkg pgbson;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pgbson;		# install via package name, for the active PG version

pig install pgbson -v 18;   # install for PG 18
pig install pgbson -v 17;   # install for PG 17
pig install pgbson -v 16;   # install for PG 16
pig install pgbson -v 15;   # install for PG 15
pig install pgbson -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION pgbson;

Usage

Sources: README, META.json 2.0.2, pgbson.control

pgbson adds a BSON data type plus BSON-aware accessors and operators. Upstream documents the package release as 2.0.2, while the extension control file still exposes SQL default version 2.0; this matches the packaging note that the dist version is ahead of the extension SQL version.

CREATE EXTENSION pgbson;

Core Access Patterns

Typed dotpath accessors walk the BSON structure directly and are the upstream-recommended fast path:

SELECT bson_get_datetime(bson_column, 'msg.header.event.ts') FROM my_table;
SELECT bson_get_bson(bson_column, 'msg.header.event') FROM my_table;
SELECT bson_get_string(bson_column, 'data.payload.product.definition.id') FROM my_table;

JSON-style operators are also supported:

SELECT (bson_column->'msg'->'header'->'event'->>'ts')::timestamp
FROM my_table;

Main Functions and Operators

• Typed getters such as bson_get_string, bson_get_int32, bson_get_int64, bson_get_double, bson_get_decimal, bson_get_datetime, bson_get_binary, and bson_get_boolean.
• bson_get_bson to return a BSON subdocument.
• bson_get_jsonb_array when a path resolves to an array and you want native jsonb array operators afterward.
• Arrow operators -> and ->> similar to PostgreSQL JSON types.
• Casts to json/jsonb using Extended JSON so type fidelity is preserved.

Interop and Indexing

Cast BSON to jsonb when you want PostgreSQL JSON operators:

SELECT (bson_get_bson(bson_column, 'msg.header.event')::jsonb) ?& ARRAY['id', 'type']
FROM my_table;

Build expression indexes on extracted paths:

CREATE INDEX ON data_collection (bson_get_string(data, 'd.recordId'));

The README also notes BSON values can round-trip byte-for-byte through bytea casts.

Caveats

• Dotpath accessors are usually faster and more memory-efficient than long -> chains because they avoid materializing intermediate substructures.
• bson_get_bson() returns NULL for scalar endpoints because simple scalars are not BSON documents.
• Upstream explicitly calls out array handling and wrong-type accessor behavior as rough edges that still need better ergonomics.