Re: JSON Patch for PostgreSQL - BSON Support?

On Mon, Aug 16, 2010 at 7:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Well, if it's not just a binary encoding of JSON, I think we can forget
> about it ... certainly it won't work in the form I was visualizing.
>
>                        regards, tom lane

I just read the spec, and BSON has a lot of bells and whistles
attached (such as labeling binary data with a subtype like UUID or
MD5). With a couple exceptions (see below), any JSON can be converted
to BSON (but the way BSON does arrays is silly: item indices are
stored as strings), but not all BSONs can be converted to JSON without
losing some type details.

Others already mentioned that you can't convert 2 billion byte long
JSON strings to BSON. Another issue is that BSON cannot encode all
JSON numbers without precision loss. JSON can hold any number
matching

'-'? (0 | [1-9][0-9]*) ('.' [0-9]+)? ([Ee] [+-]? [0-9]+)?

but BSON pidgenholes numeric values to either double, int32, int64, or
a 12-byte MongoDB Object ID. Thus, for people who expect JSON to be
able to hold arbitrary-precision numbers (which the JSON data type in
my patch can), using BSON for transfer or storage will violate that
expectation.

Now that I know more about BSON, my opinion is that it shouldn't be
used as the transfer or storage format of the JSON data type. Maybe
if someone wants to do the work, BSON could be implemented as a
contrib module, and functions could be provided in that module to
convert to/from JSON with documented caveats.

Joey Adams