Data modeling question for create linked (reference) document in CosmosDB (Mongo API)

Question

I am pretty new to both Mongodb and CosmosDB and I am trying to understand better with how to implement many to many relationship across different collections. For example, I have a contact collection, which stores the contact information. I have another Activity collection, has the activity information. So I will need to set up the relationship between contact and activity. It is many to many relationship. The linked document that I am reading from Mongodb documentation seems to be only referred to linking document within the same collection. How I can reference the contact ID from Activity collection?

For example, I have Activity collection:

{
"_id" : ObjectId("ABCDEFG"),
"ActivityName" : "Meeting",
"ActivityContact" : {
                  "$ref" : "Contact",
                  "$id" : ObjectId("1234567"),
                  "$db" : "Test"
 }

On another collection called Contact: I have data like:

{
 "_id": ObjectId("1234567"),
"ContactName" : "TestUser",
"Address" : "Some address"
}

So when I called Activity collection, it will get the data from Contact collection, sort of like left outer join from relational databases. When I ran the above insert with setting $ref, $id and $db, it was throwing the following error:

Failed to insert document.

Error:
Unsupported shard key value: { "$ref" : "Contact", "$id" : ObjectId("1234567"), "$db" : "Test" }

Per mongodb document: there are two reference types:

https://docs.mongodb.com/manual/reference/database-references/#document-references

One is manual and one is DBRef. I was trying to work out a solution for DBRef on CosmosDB. Seems there might be limited drivers available when using DBRef for Mongodb. Then I tried to use the manual reference instead.

So on the activity collection. I have the document below:

{
    "_id" : "ObjectId(\"ABCDEFG\")",
    "ActivityName" : "Meeting",
    "Department" : "Dev",
    "Contact" : [ 
        123456, 
        234567
    ]
}

{
    "_id" : 123456,
    "Department" : "Dev",
    "FirstName" : "John",
    "LastName" : "Smith",
    "Address" : "Somewhere"
}

{
    "_id" : 234567,
    "Department" : "Dev",
    "FirstName" : "Mary",
    "LastName" : "Black",
    "Address" : "Somewhere"
}

I have to add a department because it is the shard key when I create this collection on CosmosDB. Without specifying this shard key, CosmosDB won't let me create this manual reference. But even with the manual reference, it only allow me to insert one contact, it throws errors for "Error: E11000 duplicate key error collection: Failed _id or unique index constraint."

How to do resolve this issue?

Thanks,

Answer 1

CosmosDB does not support $lookup you will have to use MongoDB natively, but since you're using a managed database, I suggest you explore Atlas.

This is how it would look in MongoDB:

pipe = [
    {
        '$lookup': {
            'from': 'contact', 
            'localField': '$id', 
            'foreignField': '_id', 
            'as': 'contact'
        }
    }
]

then run it on your activity collection via connection.activity.aggregate(pipe).

Also, I suggest you avoid naming keys with a $ operator.

Answer 2

After further testing the investigation. The error I am getting is due to CosmosDB doesn't support unique sparse index on the collection. I have a unique index on one of the field and it will only enter the first contact as the unique index will take it as NULL , it will failed on all the other entries because you can only have one NULL value since sparse is not supported on COSMOS DB right now.

It is on the roadmap now.

https://feedback.azure.com/forums/263030-azure-cosmos-db?category_id=321994&filter=top&page=2

The current implementation of MongoDB allows you to create documents (such as a user document) without a field that has a unique sparse index (such as username). This is not possible with the Azure Cosmos DB Mongo API, forcing you to populate… more

PLANNED · Azure Cosmos DB Team (Product Manager, Microsoft Azure) responded Thank you Dan for your suggestion. This is currently on our road map and in plan for our upcoming development cycle which runs from January to June.

We will update here when this becomes available.

Thanks.