BSON_TO_JSON scalar function

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.

Read syntax diagramSkip visual syntax diagramBSON_TO_JSON(JSON-expression )

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" }