Add array_to_bson

[NielsHoogeveen]

I would very much like to use your code as a replacement of the code I currently have using postgresql json support, however your API doesn't seem to supply a function that works like array_to_json,

Do you have any plans to add such function?

[maciekgajewski]

I'm ay add it if it is really needed. Could you please describe your use case in more details?

In general, I shied away from implementing BSON generation functions, because one can always generate JSON and convert it to BSON. But I understand that in some scenarios this would introduce unacceptable performance costs.

[NielsHoogeveen]

The use-case for this functionality is as follows:

Right now, the result of every select query I send to the database is one row, with one column containing a complete Json document. This Json document is created using functions like array_agg, row_to_json, and array_to_json.

Example:

select array_to_json(array_agg(row_to_json(row))) as json from (
select c.country_name, c.country_code,
(
select array_to_json(array_agg(row_to_json(s))) from (
select subdivision_name, subdivision_code
from subdivision s
where s.country_code = c.country_code
order by s.subdivision_name
) s
) subdivisions
from country c
order by c.country_name
) row

This query creates a one row resultset with one column, containing a Json array of all countries, where each country contains an array of all its subdivisions.

The user application reads the Json document from the resultset and parses this Json document for further processing (eg. transformation to HTML through a templating mechanism).

I am interested to move towards a Bson implementation, to further speed up the parsing process and to reduce wire traffic.

[maciekgajewski]

I looks like you don't use JSON fields, only JSON output. To benefit from BSON, you would have to use binary format when receiving data in yout application, otherwise you'd receive JSON. (BSON uses JSON as the text representation).

The simplest thing you can do (assuming that you already use binary connection) is to simply convert bson to json in the query:

select array_to_json(row_to_json(row)))::bson as bson from (
...

If you indeed use binary transport, and you receive and process bson in your app, then and only then array_to_json could be a bottleneck, and array_to_bson would be useful.

[andrewetter]

I just ran up against this same issue as I am attempting to do end-to-end bson without json ever coming into the mix. The suggested workaround select array_to_json(row_to_json(row)))::bson as bson from ( ... throws a cannot cast type json to bson error. So, I am looking into either having my middleware manually create the bson array while iterating over the query results or potentially adding the functionality into postgresbson (preferred, but not my area of expertise).