ObjectId as string in aggregate pipeline

[Olav Reppen Breivik]

Hello,

In our MongoDB database we have separate collections with references to other collections (IE our invoice collection only has the ObjectId reference to which client it is sent to in stead of keeping multiple copies of the client inside the invoices)

We are connecting to MongoDB from Microsoft PowerBI using Apache Drill and its ODBC-driver. To make this work I have created some simple views like the following that flattens out my data to work with Drill:

db.runCommand( {create : 
    "CreditorMappings",
    viewOn : "yggdrasilconfiguration",
    pipeline : [
    {$unwind : '$CreditorsMapping'},
    {$project : {
        _id : 1, 
        OrganizationId : '$OrganizationId', 
        PrimaryId : '$CreditorsMapping.PrimaryId', 
        CreditorId : '$CreditorsMapping.CreditorId', 
        ApplicationId : '$CreditorsMapping.ApplicationId'
    },
    {$match : {PrimaryId : {$ne : ""}}}
]
})

_id, OrganizationId and ApplicationId are ObjectId's. The trouble is that Drill is unable to join on ObjectId's and PowerBI doesn't accept binary data as references, so I am unable to join data between the different collections. It should be possible to convert the ObjectId's to strings, but all my attempts have failed.

I have tried these approaches to no avail:

db.getCollection('yggdrasilconfiguration').aggregate([
    {$unwind : '$CreditorsMapping'},
    {$project : {
        _id : 0, 
        OrganizationId : '$OrganizationId',
        ToStringedOrganizationId : '$OrganizationId'.toString(),
        StrOrganizationId : '$OrganizationId'.str,
        TojsonOrganizationId : tojson('$OrganizationId'), 
        PrimaryId : '$CreditorsMapping.PrimaryId', 
        CreditorId : '$CreditorsMapping.CreditorId', 
        ApplicationId : '$CreditorsMapping.ApplicationId'}
    },
    {$match : {PrimaryId : {$ne : ""}}}
])

Here I use the toString() method, the .str suffix and the tojson() method on the OrganizationId, but the results of this looks like this:

{
    "OrganizationId" : ObjectId("542d53fc669a710728349cc8"),
    "ToStringedOrganizationId" : ObjectId("542d53fc669a710728349cc8"),
    "StrOrganizationId" : undefined,
    "TojsonOrganizationId" : "\"$OrganizationId\"",
    "PrimaryId" : "915568",
    "CreditorId" : "7626",
    "ApplicationId" : ObjectId("542e5dca669a7107d8ac1da5")
}

Is it possible to do what I want? To be clear I just want the string "542d53fc669a710728349cc8" without the surrounding ObjectId() from my OrganizationId field in this example.

[Olav Reppen Breivik]

I have found the same question on stackoverflow, but the resolution to switch from aggregation pipeline to map reduce doesn't work for me. This is because I have to use views so that I can query it via SQL using Apache drill. (If anybody know of any better method of connecting Power BI to mongodb let me know, but this is the only viable one we have found without spending thousands of dollars in licencing.)

Kind regards,

Olav

[Wan Bachtiar]

It should be possible to convert the ObjectId’s to strings, but all my attempts have failed.

Hi Olav,

Currently there is no way to convert ObjectId to string format inside the aggregation pipeline. However there’s an open ticket to track this request SERVER-24947, please feel free to upvote/watch for updates.

If it’s possible for your use case, you could perhaps add another field to join the two collections in a format that Apache Drill and PowerBI could read.

Regards,

Wan.

​

[Olav Reppen Breivik]

Thank you for your reply. I have voted on the issue you linked. We ended up using another driver, which didn't regard ObjectId's as binary data. My previous reply haven't been approved. Don't know if it is because I named the driver we chose. If anyone wants to know which driver we ended up using you can PM me.

 II​