The MongoDB driver supports the custom function escape CAST_TO_NATIVE. The CAST_TO_NATIVE function escape can be used in a filter to select or insert a value of a specific native type.
In some cases, a value with a specific native type can be concealed or obscured because the driver describes a field with inconsistent native types as a column with a single SQL type. For example, the driver maps a MongoDB _id field with the native types String and ObjectId to a relational _ID column with the VARCHAR type. In this context, the CAST_TO_NATIVE function escape allows users to send a value as it is defined in the MongoDB database, rather than how it is described in the relational model of the data.
Currently, CAST_TO_NATIVE can only be used with the ObjectID type in SELECT statement filters and literal INSERT values.
Syntax
{fn CAST_TO_NATIVE('value','native_type')}
where:
value
is the value to be selected or inserted.
native_type
is the native type that in part defines the value.
Example Select
The following statement selects records from Account where the _ID field has an ObjectID value of 507f1f77bcf86cd799439011.
SELECT * FROM Account WHERE _ID = {fn CAST_TO_NATIVE('507f1f77bcf86cd799439011','objectid')}
Example Insert
The following statement inserts the ObjectID value 507f1f77bcf86cd799439011 into the _ID field.
INSERT INTO Account(_ID) VALUES ({fn CAST_TO_NATIVE('507f1f77bcf86cd799439011','objectid')})
