SQL Like join on TypeMap value

1

Hello :slight_smile:

I would like to perform a simple join between two collections but one of the join criteria is in a typeMap column: the pseudo code would be something like this :

SELECT *
FROM “mongodbs statistical”.userProfile.transaction as “tr”
left join “mongodbs statistical”.userProfile.user as “user” on “tr”.owner.’_id’ = “user”._id

Where “tr”.owner.’_id’ is like this :

and “user”._id is like that :

Is this possible or not with Dremio ? If not how can I achieve such a miracle :stuck_out_tongue:

Thanks a lot :slight_smile:

2

Did you try extracting the owner._id into a column and using that to build the join?

3

No I’m sorry I literally have no idea how to do this.
I’m using dremio’s PowerBI connector to just retrieve some data from a MongoDB rep.
How would that work?

Edit: Oh if you meant by editing the MongoDB collection, I’m affraid I don’t have such a flexibility since I’m doing this for a client that has not a lot of resources. Basically I’m building some business intelligence report on top of a live MongoDB replica.

4

Do you have access to Dremio or are you only able to connect via ODBC?

5

I have access to Dremio :slight_smile:

6

Ok cool.

If you look through this tutorial you’ll find an example of extracting a nested value into it’s own column. I’m not sure this will solve what you’re trying to do, but worth a shot.

7

Thanks for trying but I could extract it and save the dataset, but it’s not scalable.
Although, I managed to make a join based on the nested value (please find the running example which is not the same request as the first one but the philosophy remains identical) :

select count(*) as "Total_listings_from_shops"

FROM "mongodbs statistical".product.product

WHERE product.owner."$id" in (SELECT distinct userId as id
FROM  "mongodbs statistical".userProfile.transaction
WHERE type = 'STORE_PLAN' AND status = 'COMLETED' AND currency = 'DH')

But the thing is that I have this error when I run the query :

FUNCTION ERROR: Unexpected byte 0xaa at position 8 encountered while decoding UTF8 string. SqlOperatorImpl HASH_JOIN Location 1:0:3 Fragment 1:0 [Error Id: e614f955-2664-4171-9a0d-76e56b03f039 on 192.168.1.19:31010]

Any idea how to help?