Add support for MongoDB Database References

[ebyhr]

• Stackoverflow: https://stackoverflow.com/questions/60713849
• MongoDB referece: https://docs.mongodb.com/manual/reference/database-references/

presto> select * from mongodb.test.test_ref;
 $ref |                 $id                 | $db  
------+-------------------------------------+------
 test | 5e 71 93 9b 7d 7f e6 32 5a 0b 33 07 | test 

[kristina-head]

Please may I work on this?

[findepi]

@kristina-head sure, go for it!

[achyudh]

@kristina-head are you still working on this? I’d like to give this a shot

[kristina-head]

@achyudh it's all yours :)

[jasonyanwenl]

Hi @achyudh are you still working on this? I would like to take a look at this issue as well.

[achyudh]

@jasonyanwenl nope, feel free to take it up

[jasonyanwenl]

Hi @ebyhr, may I ask which part of the code is a good place to start? Thanks!

[ebyhr]

@jasonyanwenl I think you can start with MongoSession#getTableMetadata that has "guess fields" mechanism.

[jasonyanwenl]

Hi @ebyhr, currently I am trying to get more background about this issue. I have read two links you have shared. In my sense, DBRefs are kind of like pointers to another document in MongoDB. One thing I am confused is the example you are given:

presto> select * from mongodb.test.test_ref;
 $ref |                 $id                 | $db  
------+-------------------------------------+------
 test | 5e 71 93 9b 7d 7f e6 32 5a 0b 33 07 | test 

Essentially, I don't know what is the exact requirement for solving this issue. Here are two versions I could come up with:

1. Do you mean that the above SQL query result is just what we expect to have? And if so, do you mean that the test_reftable is automatically created as long as the test table is created. And the fields inside test_ref is auto-filled with $ref, $id and $db?

2. Do you mean when a document is queried, we need to automatically check if there is any DBRefs inside this doc and if so we need to automatically retrieve the referenced doc and finally return the entire doc along with referenced doc as the query result?

Which way is correct? Or I totally misunderstand any points? Thanks!

[ebyhr]

@jasonyanwenl Thanks for looking into the details.

1. Though I almost forgot how I created the above example, test_ref should be created beforehand. If we use DBRef in MongoDB, the field has $ref, $id and $db. You can reproduce this issue by this steps:

• Start MongoQueryRunner
• Run mongo shell docker exec -it <container id> mongo
• Prepare data:

use users;

db.creators.insert({
    "_id": ObjectId("5126bc054aed4daf9e2ab772"),
    "name": "Broadway Center",
    "url": "bc.example.net"
})

db.test_ref.insert(
{
  "_id" : ObjectId("5126bbf64aed4daf9e2ab771"),
  "col1" : "foo",
  "creator" : {
                  "$ref" : "creators",
                  "$id" : ObjectId("5126bc054aed4daf9e2ab772"),
                  "$db" : "users"
               }
})

• Select the table from Presto. You will see creator column is missing because the type is DBRef

select * from mongodb.users.test_ref;

2. It is the final goal, but let's start with simple step. The minimum goal is showing DBRef fields so that we can use it on join condition.

[academy-codex]

@ebyhr I have got this working with some nits.
If no one is working, I can take this up.

Sample Output:
trino:test> select * from test_ref;
col1 | creator
------+---------------------------------------------------------------------------------------
foo | {id=51 26 bc 05 4a ed 4d af 9e 2a b7 72, databaseName=users, collectionName=creators}
(1 row)

Sample Join:
trino:test> select * from creator, test_ref where creator._id=test_ref.creator.id;
name | url | col1 | creator
-----------------+----------------+------+----------------------------------------------------------------------------
Broadway Center | bc.example.net | foo | {id=51 26 bc 05 4a ed 4d af 9e 2a b7 72, databaseName=users, collectionName
(1 row)

[academy-codex]

@ebyhr I have got this working with some nits.
If no one is working, I can take this up.

Sample Output:
trino:test> select * from test_ref;
col1 | creator
------+---------------------------------------------------------------------------------------
foo | {id=51 26 bc 05 4a ed 4d af 9e 2a b7 72, databaseName=users, collectionName=creators}
(1 row)

I am currently, mapping the $ref->collectionName, $db-> databaseName and $id->id.
However, I also see that MongoPageSource.writeSlice(..) has this logic:
if (type.equals(OBJECT_ID)) {
type.writeSlice(output, wrappedBuffer(((ObjectId) value).toByteArray()));
}

Any reason to use byte array here ?

[ebyhr]

The reason is that MongoDB ObjectId type is mapped to Slice internally. Does this answer your question?
https://github.com/trinodb/trino/blob/master/plugin/trino-mongodb/src/main/java/io/trino/plugin/mongodb/ObjectIdType.java

[academy-codex]

The reason is that MongoDB ObjectId type is mapped to Slice internally. Does this answer your question?
https://github.com/trinodb/trino/blob/master/plugin/trino-mongodb/src/main/java/io/trino/plugin/mongodb/ObjectIdType.java

I see. Got it. Thanks :)