Convert BSON to real JSON
Florian Apolloner found genbson(...)::json outputs dates as ISODate(...). Luis Marques suggested TO_CHAR on the date, or parsing the MongoDB-style JSON, or an RFE. Florian saw the field name was lost; Luis gave a derived-table workaround aliasing the TO_CHAR result as some_date. Florian found it viable, though still to check it works in a trigger.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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