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 :
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
Thanks a lot
Did you try extracting the owner._id into a column and using that to build the join?
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.
Do you have access to Dremio or are you only able to connect via ODBC?
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.
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?