JSON support in postgres is superb but sometimes you really want decimal, date, and binary types, "carefree" UTF8 string handling (i.e. no escaping), and robust roundtrippability. So I made an extension for BSON.
The code here is really well designed. This project can serve as a tutorial on how to build a Postgres extension. Have a look at this:<p><a href="https://github.com/buzzm/postgresbson/blob/main/pgbson--2.0.sql">https://github.com/buzzm/postgresbson/blob/main/pgbson--2.0....</a><p>and this:<p><a href="https://github.com/buzzm/postgresbson/blob/main/pgbson.c">https://github.com/buzzm/postgresbson/blob/main/pgbson.c</a><p>Really nice stuff.
This is really cool. A common use case for me is building JSON objects directly in my query, for example to return a list of json objects. Usually this means date columns lose their type, is there a way of returning bson like with jsonb_build_object that keeps this types?
Perhaps this is an opportunity to ask somebody who might know about BSON performance. As a POC/stress test for work I added two multi-GB datasets to Postgres (as JSONB) and to Mongo (BSON). While trying to query approximately a hundred megabytes of data (a few hundred documents) from each I found that Postgres executed the query and decoded the JSON data in under a second, while it took Mongo a few seconds.
Does this mean that BSON is slow to deserialize? Or perhaps it is not related to serialization? I was quite confused.
Listed status is "experimental" but only two relatively minor commits in the last two years. Maybe it's more stable than that implies or the author is looking to pick it back up?