The BSON_TO_JSON function converts a string that contains data that is formatted as BSON to a character string that contains data that is formatted as JSON.

Although the schema for this function is SYSIBM, the function cannot be specified as a qualified name.

JSON-expression

Specifies an expression that returns a binary string value.
It must contain formatted BSON data (SQLSTATE 22032).

If JSON-expression can be null, the result can be null;
if JSON-expression is null, the result is the null value.

Notes

• If parameter markers are not explicitly cast to a supported data type, an error is returned (SQLSTATE 42815)

Example

1. Retrieve a JSON document in string format, from a table where the data is stored in binary representation.

   SELECT JSON_FIELD FROM TESTJSON;
   
   JSON_FIELD
   --------------------
   x'16000000024E616D65000700000047656F7267650000'

   To extract the contents of a JSON field, use the BSON_TO_JSON function.

   SELECT BSON_TO_JSON(JSON_FIELD) FROM TESTJSON;
   
   1
   --------------------
   { "Name" : "George" }