Mongodb BI connector referenced docume

Question

I'm using MongoDB BI connector starting the mongosqld process in stand alone mode (it generates the schema file ad store it in memory).

The problem is that the structure mapped doesn't contains in the tables the foreign key columns towards referenced document. For example, below is reported a COLLECTION-A in which a COLLECTION-B is referenced.

The table COLLECTION-A mapped in the schema doesn't contains the reference to the collectionB.

"_id" : ObjectId("63a172ff55b4250001c59477"),
    "collectionB" : {
        "$ref" : "COLLECTION_B",
        "$id" : ObjectId("5bf7e8c33766cd0001e9578a")
    }

Have you encountered this kind of problem end how you solved it?

Thanks

I tried to configure the mongosqld process to use samplesize = 0 in order the sample all the documents in the collections.

Answer 1

You can solve partially this issue creating "view" with aggregation $lookup stage ( check example here ) and access the view from the BI-connector client , but if collection si relatively big this is not very performance optimal solution.