Azure Cosmos Db document partition key having duplicate, but find duplicate document with combination of other columns

Question

I have below document JSON (pasted partial JSON, actual JSON will be complex and embedded). The JSON has Code as ParitionKey, I am trying to build No SQL database documents by migrating my sql tables, and I will have Code, Type making Unique row, as you can see below Code = 4 is duplicated with different Type and id I just generated GUID (not sure on id field so generated GUID and assigned to it).

we only have two values for Type filed, it's either RI or NRI for entire data, and Code is duplicated like below sample data Code:4, but combination of Type & Code fields make it unique.

Example JSON:

{
 "id" : "88725628-2a9a-4fc7-90ed-29c5ffbd45fa"
 "Code": "4",
 "Type": "RI",
 "Description": "MAC/CHEESE ",
},
{
 "id" : "88725628-9a3b-4fc7-90ed-29c5ffbd34sk"
 "Code": "8",
 "Type": "RI",
 "Description": "Cereals",
},
{
 "id" : "88725628-6d9f-4fc7-90ed-29c4ffbd87de"
 "Code": "4",
 "Type": "NRI",
 "Description": "Christmas Deal",
}

In NoSQL cosmos document db, I couldn't use two columns as partition key, so I have only code as Partition key, but when I am trying to insert into Cosmos Db how do I check if not exists then only insert or else I would end up creating duplicate documents:

CreateItemAsync --> I need a way to check if the document already exists if not then create

I have below code to check and if not found create Item

try
{
  // Read the item to see if it exists.  
  ItemResponse<Item> itemResponse = await this.container.ReadItemAsync<Item>(itm.Id, new PartitionKey(itm.Code));
                
}
catch (CosmosException ex) when (ex.StatusCode == HttpStatusCode.NotFound)
{
  // Create an item in the container representing the Andersen family. Note we provide the value of the partition key for this item, which is "Andersen"
  ItemResponse<Item> itemResponse = await this.container.CreateItemAsync<Item>(itm, new PartitionKey(itm.Code));                       
}

But from above code in ReadItemAsync parameters, how do I know id parameter as it is a GUID randomly generated on every insert, is there a better way to utilize id property before insert into Cosmos DB, so it can be utilized while ReadItemAsync ?

second parameter is paritionKey, If I give code as partition key, it wouldn't work as expected as Code can be duplicated with different "Type" values and it's valid, but Code & Type together makes it unique and we shouldn't allow another document to be inserted if code and type are same.

How do I do it in Cosmos db insert ? I have below questions:

id field --> can I generate GUID and save document or id filed has any purpose which can be utilized during reads ?
Is it ok to pick a partition key which can potentially have duplicates like Code field.
How do I check document exists before insert with above qualifiers as Code filed can be duplicated but only With Type it makes it unique ?

Any suggestions ?

Mark Brown Mark Brown · Accepted Answer · 2020-12-21T20:05:29

If code and type make a unique row then you should use the value of type for id as well rather than generating a GUID because in Cosmos DB the combination of your partition key and id must be unique.

Then when you do an insert, if the data is already there it will throw an exception which you can catch. For reads, if you know the value for code and type, you can use these to perform a point read to get a single row of data, rather than using a query. This is the most efficient way to fetch data in Cosmos DB.

It is fine to have duplicates for partition key values. You only need to make sure that you have less than 20GB of data for each partition key value.

Azure Cosmos Db document partition key having duplicate, but find duplicate document with combination of other columns

1 Answers