Convert BSON to real JSON

Hi there, I am evaluating the BSON data types in informix and want to process the data further in my application. Sadly a query like this select first 1 genbson( ROW(some_date), 1, 1 )::json from some_table results in: {"some_date":ISODate("1980-07-18T00:00:00.000Z")} Is there any way to get some_date as string instead of ISODate for further processing in JSON aware libraries? Thanks, Florian

The format seems to be MongoDB focused. If your application can parse the MongoDB json extensions, then that would be the better way. Or you can convert the datetime to a string with the 'TO_CHAR' function: SELECT FIRST 1 genbson( ROW( TO_CHAR( some_date, '%Y-%m-%dT%H:%M:%S.%F3Z' ) ), 1, 1 )::JSON FROM some_table; Maybe also open an RFE asking to add that functionality to the genbson function.

Thanks, I tried TO_CHAR but that looses the "name" of the field and I get a "" as key in the JSON document :/ -- can I somehow provide an alias value again?

You can use a table expression to work around that: SELECT FIRST 1 genbson( ROW( vt1.some_date ), 1, 1 )::JSON FROM ( SELECTTO_CHAR( t1.some_date, '%Y-%m-%dT%H:%M:%S.%F3Z' ) AS some_date FROM some_table AS t1) AS vt1; I have no idea what kinda of performance hit you will get with this.

Ah that actually seems like a viable approach; I just have to check if this works in a trigger too :D (Ie if SELECT * FROM OLD/NEW) works. Thanks for your help so far. Florian