Cosmosdb with multiple partition key

Question

Cosmosdb with multiple partition key

Raja 81

Hello Team,
I have cosmosdb setup for one of my requirements. The cosmosdb is accessed using its REST API functionality.
The cosmosdb is partitioned one key called let's cid. So overall the REST call looks like ,

https://{{DocumentDBHost}}/dbs/mydb/colls/mycol/docs/123

This works perfectly fine.
In the JSON document we also have few more fields/attributes like oid and sid.
Now we have an additional requirement that we want to query on multiple such ids. Suppose we have one attribute as oid (value 222) and one attribute as sid(333). If user uses any of these ids, the cosmosdb should return the JSON.

So essentially if user passes any of these
https://{{DocumentDBHost}}/dbs/mydb/colls/mycol/docs/123 //For cid
https://{{DocumentDBHost}}/dbs/mydb/colls/mycol/docs/222 //For oid
https://{{DocumentDBHost}}/dbs/mydb/colls/mycol/docs/333 //For sid

User wants this to get resolved internally. So if he passes any number as id, cosmosdb should search across these 3 type of ids ( attributes of JSON) and should be able to resolve it or find the correct document.

What's the best way to achieve it? Is there any way to have multiple partition keys at the same time which can resolve it?

Thanks,
Raja

Anurag Sharma 17,311 Reputation points

2022-01-25T11:11:35.607+00:00
Hi @Raja , welcome to Microsoft Q&A forum.

Multiple partition keys are not supported in Azure Cosmos DB but we can create a synthetic partition key which is the concatenation of multiple properties. However this would also not fit in your requirement.

The rest api call mentioned here in the thread cannot fetch the result based on multiple properties but we can use a POST request with parameters as mentioned below with appropriate headers:

POST https://{ {DocumentDBHost}}/dbs/mydb/colls/mycol/docs/

Headers:

x-ms-documentdb-isquery: True x-ms-documentdb-query-enablecrosspartition: True Content-Type: application/query+json { "query": "SELECT c.cid,c.sid,c.oid FROM mycol c WHERE c.cid = @id or c.sid = @sid or c.oid = @oid", "parameters": [ {"name": "@id", "value": 123}, {"name": "@sid", "value": 333}, {"name": "@oid", "value": 222} ] }

Referenced Article: Query Documents

Please let us know if this helps or else we can discuss further on the same.
Raja 81 Reputation points

2022-01-25T11:52:44.163+00:00

Hello @AnuragSharma-MSFT
Thanks for the prompt response. Yes, I'm aware of the post requests, the only problem here is, it looks like a SQL query which might be little complex for end users to try with.

Okay, what if we create one more layer(other program/azure function/anything else?) in between which takes the id as input and then creates a post requests out of it as you have mentioned in your response.

Could you please suggest some good option of creating this "layer"?
Anurag Sharma 17,311 Reputation points

2022-01-27T05:25:18.033+00:00

Thanks for replying back. Yes we can create App service, Azure function etc. which will in turn call the Azure Cosmos DB rest apis.

We can choose the programming language which we are comfortable with while creating these services. Please let us know if there is any preference and guidance we need on the same.
Raja 81 Reputation points

2022-01-31T06:24:39.153+00:00

Hello @AnuragSharma-MSFT ,
Thanks for the response. Can something like below be achieved with CosmosDB?

https://{ {DocumentDBHost}}/dbs/mydb/colls/mycol/docs/{Id}[?type]

Anurag Sharma 17,311 Reputation points

2022-01-25T11:11:35.607+00:00

Hi @Raja , welcome to Microsoft Q&A forum.

Multiple partition keys are not supported in Azure Cosmos DB but we can create a synthetic partition key which is the concatenation of multiple properties. However this would also not fit in your requirement.

The rest api call mentioned here in the thread cannot fetch the result based on multiple properties but we can use a POST request with parameters as mentioned below with appropriate headers:

POST https://{ {DocumentDBHost}}/dbs/mydb/colls/mycol/docs/

Headers:

x-ms-documentdb-isquery: True x-ms-documentdb-query-enablecrosspartition: True Content-Type: application/query+json { "query": "SELECT c.cid,c.sid,c.oid FROM mycol c WHERE c.cid = @id or c.sid = @sid or c.oid = @oid", "parameters": [ {"name": "@id", "value": 123}, {"name": "@sid", "value": 333}, {"name": "@oid", "value": 222} ] }

Referenced Article: Query Documents

Please let us know if this helps or else we can discuss further on the same.
Raja 81 Reputation points

2022-01-25T11:52:44.163+00:00

Hello @AnuragSharma-MSFT
Thanks for the prompt response. Yes, I'm aware of the post requests, the only problem here is, it looks like a SQL query which might be little complex for end users to try with.

Okay, what if we create one more layer(other program/azure function/anything else?) in between which takes the id as input and then creates a post requests out of it as you have mentioned in your response.

Could you please suggest some good option of creating this "layer"?
Anurag Sharma 17,311 Reputation points

2022-01-27T05:25:18.033+00:00

Thanks for replying back. Yes we can create App service, Azure function etc. which will in turn call the Azure Cosmos DB rest apis.

We can choose the programming language which we are comfortable with while creating these services. Please let us know if there is any preference and guidance we need on the same.
Raja 81 Reputation points

2022-01-31T06:24:39.153+00:00

Hello @AnuragSharma-MSFT ,
Thanks for the response. Can something like below be achieved with CosmosDB?

https://{ {DocumentDBHost}}/dbs/mydb/colls/mycol/docs/{Id}[?type]

Answer 1

As your description mentioned, you have a partition key called cid. This cid is the only partition key CosmosDB can have and once it was decided at the initialization it cannot be changed anymore.

Since you have oid and sid, they probably can be properties for indexing purposes and you need to design them in this way instead of the partition key. which means you could wrap the basic REST API with your own method including something like sql query as mentioned above and expose the new API to the end-users.

Cosmosdb with multiple partition key

1 answer

Activity