Convert VARBINARY to VARCHAR

[paulo]

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

[balaji.ramaswamy]

Hi @paulo

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

share-query-profile

Thanks,
@balaji.ramaswamy

[paulo]

Hey, Balaji.
Here’s the fileac7d599e-bafe-4a81-953d-c428cf4f99e3.zip (2.8 KB)

[balaji.ramaswamy]

Thanks @paulo

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

Thanks,
@balaji.ramaswamy

[paulo]

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

[balaji.ramaswamy]

Hi @paulo

Let me reproduce this internally and get back to you

Thanks,
@balaji.ramaswamy

[paulo]

Thank you, Balaji. I’ll be waiting

[gendigbadig]

any updates for this issue?

[moa101]

any resolutions for this issue?

[bogini]

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

[balaji.ramaswamy]

@bogini

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

Thanks
@balaji.ramaswamy

[bogini]

Hi Balaji,

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

[balaji.ramaswamy]

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

Thanks
@balaji.ramaswamy

[bogini]

Hi @balaji.ramaswamy, still no luck.

When decoded it should read like 58080ff4ebbf470003ca9f7b. More info here: https://docs.mongodb.com/manual/reference/method/ObjectId/

[steven]

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

lower(to_hex(_id))

[bogini]

It worked!! Thank you so much @steven