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.
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?
Can you please send me the profile of the failed query?
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:
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>
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: https://docs.mongodb.com/manual/reference/method/ObjectId/
It looks like you want the hexadecimal representation of the binary value. To get that, you can run:
It worked!! Thank you so much @steven