BSON2JSON scalar function

The BSON2JSON user-defined function converts a JSON document in binary format (extended BSON) into a readable JSON text format.

Read syntax diagramSkip visual syntax diagramBSON2JSON( inbson)

The schema is SYSTOOLS.

inbson
An expression that returns a document in binary format (extended BSON) that is no larger than 16 MB.

The result is a CLOB (16 MB) that contains a JSON document in a readable text format.

If the input value does not contain a valid BSON formatted expression, or if the converted value cannot fit into a 16 MB CLOB, an error is returned.

Notes for BSON2JSON

CCSID rules
The BSON2JSON function follows the CCSID rules for user-defined functions, which means that it returns a string data type in the appropriate CCSID of the application encoding scheme. So, you might need to use the CAST function to avoid problems with the encoding of returned data.
Start of changeDate value restrictionsEnd of change
Start of changeWhen $DATE is used in the JSON document, only date values from 01 January 1970 through 31 December 2037 are supported.End of change

Example

The following SQL statement uses BSON2JSON to convert the content of the BLOB column that is named PROFILE to a readable format as part of a SELECT statement. The example assumes that PROFILE contains data that was inserted in a valid BSON format: 

SELECT CUSTID, CITY, SYSTOOLS.BSON2JSON(PROFILE)
FROM CUSTOMER_LIST 
WHERE CUSTID = '12345';