Hey guys. How’s it going?
I’m stuck in a problem related to joining two fields in a query.
One of them is a MongoDB field, which Dremio is reading as a VARBINARY type, and another one is on a MySQL table, formatted as a VARCHAR type. Here’s an example of how the field is shown on MongoDB, and how it is displayed on Dremio’s interface.

MongoDB: ObjectId(“5a268eefe94fa4100a59facf”)
Dremio: WO6J4kqVfgANUuc3

What I’m supposed to do:
The information inside of the parenthesis (in this case, “5a268eefe94fa4100a59facf”) is what the MongoDB sends to MySQL, so, I need to join this two fields. However, I’m trying to convert this field using the CAST function and it’s not working. Do you guys have any idea about what I could do to solve this problem?

Best regards

Hi @paulo

Can you please send me the profile of the failed query?



Hey, Balaji.
Here’s the (2.8 KB)

Thanks @paulo

Another quick Q, what is the type of the column defined in MongoDB that Dremio reads as VARBINARY?


It’s an “ObjectId” field. Here’s how it has been stored on MongoDB:

Hi @paulo

Let me reproduce this internally and get back to you


Thank you, Balaji. I’ll be waiting

any updates for this issue?

any resolutions for this issue?

I’m also looking for a solution here. Any updates?


Can you please try select convert_from(column_name,‘utf8’,‘x’) from <table_name>


Hi Balaji,

Thanks for your reply but it doesn’t seem to be working as expected:

I it possible id is a number or int. Can you please try cast instead? or in the convert_from, instead of ‘X’ use something like 9


Hi @balaji.ramaswamy, still no luck.

When decoded it should read like 58080ff4ebbf470003ca9f7b. More info here:

It looks like you want the hexadecimal representation of the binary value. To get that, you can run:


1 Like

It worked!! Thank you so much @steven