Joining MongoDB collections not working

Get Help
1

Hello all, we have a bunch of MongoDB collections we have connected to Metabase. We are trying to connect tables across 2 collections. After a lot of debugging, one issue we are facing is that the primary key in at least one of the tables is an objectid, hence the join is not working. I'm trying to create a Custom Column by converting this objectID into a string, but there seems to be no function available to do so. Please advice on what steps I can take, either on MongoDB side and/or Metabase side to get this to work.

2

Troubleshooting info?

3

Since there is no to_string function I can use in the custom field, I tried to use concat.
= concat(" ", ID)
Where field "ID" is of type "type/MongoBSONID"

Command failed with error 16702 (Location16702): '$concat only supports strings, not objectId' on server mongo-b2c.demo:27017. The full response is {"operationTime": {"$timestamp": {"t": 1685359529, "i": 1}}, "ok": 0.0, "errmsg": "$concat only supports strings, not objectId", "code": 16702, "codeName": "Location16702", "$clusterTime": {"clusterTime": {"$timestamp": {"t": 1685359529, "i": 1}}, "signature": {"hash": {"$binary": "AAAAAAAAAAAAAAAAAAAAAAAAAAA=", "$type": "00"}, "keyId": {"$numberLong": "0"}}}}

4

Here is a solution that works well:

[
    {
        "$lookup": {
            "let": {
                "userObjId": {
                    "$toObjectId": "$userId"
                }
            },
            "from": "user",
            "pipeline": [
                {
                    "$match": {
                        "$expr": {
                            "$eq": [
                                "$_id",
                                "$$userObjId"
                            ]
                        }
                    }
                }
            ],
            "as": "mydata_join_user"
        }
    },
    {
        "$unwind": {
            "path": "$mydata_join_user",
            "preserveNullAndEmptyArrays": true
        }
    },
    {
        "$project": {
            "_id": "$_id",
            "userId": "$userId",
            "topic": "$topic",
            "date": "$date",
            "action": "$action",
            "_class": "$_class",
            "user___id": "$mydata_join_user._id",
            "user__firstName": "$mydata_join_user.firstName",
            "user__lastName": "$mydata_join_user.lastName",
            "user__email": "$mydata_join_user.email",
            "user__organization": "$mydata_join_user.organization",
            "user__role": "$mydata_join_user.role"
        }
    }
]

Enjoy !